Help ordering table query 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 &gt; MODERATE &gt; 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.
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 &quot;most upper occurence&quot; 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.