To order then that the same genes are together you add the Gene also to the ORDER BY.
The order of the column defines which will be sorted first
Iy you nned a special order for the Genes you need another FILED that gives a sorting for Genes like you did in Impact
Schema (MySQL v8.0)
CREATE TABLE testtable (
`Gene` VARCHAR(5),
`Impact` VARCHAR(8),
`Frequency` FLOAT
);
INSERT INTO testtable
(`Gene`, `Impact`, `Frequency`)
VALUES
('SCN1A', 'LOW', '0.8'),
('CFTR', 'MODETARE', '0.1'),
('ADA', 'HIGH', '0.2'),
('ACADM', 'LOW', '0.1'),
('SCN1A', 'HIGH', '0'),
('CFTR', 'LOW', '0.5');
Query #1
SELECT *
FROM testtable
ORDER BY Gene DESC,FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency;
| Gene | Impact | Frequency |
| ----- | -------- | --------- |
| SCN1A | HIGH | 0 |
| SCN1A | LOW | 0.8 |
| CFTR | MODETARE | 0.1 |
| CFTR | LOW | 0.5 |
| ADA | HIGH | 0.2 |
| ACADM | LOW | 0.1 |
View on DB Fiddle
