Help ordering table query
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.