mysql – Help ordering table query


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