Help ordering table query

Question

I have a MySQL table with this structure:

Gene  |  Impact  |  Frequency
---------------------------
SCN1A | LOW      |  0.8
---------------------------
CFTR  | MODERATE |  0.1
---------------------------
ADA   | HIGH     |  0.2
---------------------------
ACADM | LOW      |  0.1
---------------------------
SCN1A | HIGH     |  0
---------------------------
CFTR  | LOW      |  0.5
---------------------------

I need to order the table in a SELECT query according values in 'Impact' column (HIGH > MODERATE > LOW) and Frequency descending values when 'Impact' values are the same, like this:

Gene  | Impact   |  Frequency
---------------------------
SCN1A | HIGH     | 0
---------------------------
ADA   | HIGH     | 0.2
---------------------------
CFTR  | MODERATE | 0.1
---------------------------
ACADM | LOW      | 0.1
---------------------------
CFTR  | LOW      | 0.5
---------------------------
SCN1A | LOW      | 0.8
---------------------------

Up to this point the query must contain the next statement:

ORDER BY FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency

The problem is that, after ordering, I need to go up those rows that have same value in first column in order to have all rows with same Gene name together, like this:

Gene  | Impact   |  Frequency
---------------------------
SCN1A | HIGH     |  0
---------------------------
SCN1A | LOW      | 0.8
---------------------------
ADA   | HIGH     | 0.2
---------------------------
CFTR  | MODERATE | 0.1
---------------------------
CFTR  | LOW      | 0.5
---------------------------
ACADM | LOW      | 0.1
---------------------------

Does anyone can help me with the query?

Imagine that ADA has Frequency=0. Should it be returned before the SCN1A group? If not, then additionally imagine that SCN1A with Impact=HIGH has Frequency=0.1`.

The order of the gene does not matter when both have the same Impact and Frequency value, although the ideal would be to order them alphabetically, so ADA would be first in your example. If SCN1A has Impact HIGH and Frequency 0.1 should be in third position as found in the example.

Answer 1

WITH
-- enumerate all rows according to specified ordering
cte1 AS (SELECT *, ROW_NUMBER() OVER (ORDER BY FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency) rn
         FROM test),
-- find the most upper occurence for each `Gene`
cte2 AS (SELECT Gene, MIN(rn) rn
         FROM cte1
         GROUP BY Gene)
-- select desired data
SELECT t1.*
FROM test t1
JOIN cte2 USING(Gene)
-- use found "most upper occurence" for groups pre-sorting, 
-- then apply specified sorting
ORDER BY cte2.rn, FIELD(Impact,'HIGH','MODERATE','LOW'), Frequency;

fiddle

---

the order of the gene does not matter when both have the same Impact and Frequency value, although the ideal would be to order them alphabetically, so ADA wuould be first in your example.

If so simply add , Gene to the end of ordering expression.

Answer 2

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