Keep one row per ID while keeping previous order (from ORDER BY) I'm actually working on a big query and stuck at the very end.
I'm working on MySQL 5.7.

ID // SubID // Criteria 1 // Criteria 2 // ... // Critera n
1 //   2   //    x        //    y      //  ... //   z
1 //   3   //    x        //    y      //  ... //   z
1 //   1   //    x        //    y      //  ... //   z
2 //   1   //    x        //    y      //  ... //   z
3 //   1   //    x        //    y      //  ... //   z


I would need for each ID the best (ID, SubID) pair, based on the existing order which is based on sorting on criteria 1 to n.

I had a look on several solutions but:


GROUP BY picks randomly any (ID, SubID) pair and doesn't take into account the existing order
GROUP BY + ORDER BY is not sufficient as I need to sort on complex criteria
I can't recover the subID info if I use DISTINCT


I have the feeling that the solution is fairly simple but I'm really stuck and I can't find similar problems/solutions on the Internet (maybe I just need some sleep ;) )

Just to make it clear: I need to keep 1 row per ID but in the same order as before, and to return at least ID // SubID (but it would be perfect if it could return all the criteria as well).

Thank you in advance for your help guys!

EDIT

Ok so to add a little bit of context here, my criteria are either boolean or float values that I computed in a subquery.

It looks like:

SELECT * FROM
(SELECT *,
CASE(formula) THEN 1 ELSE 0 as criteria_1,
CASE(formula) THEN 1 ELSE 0 as criteria_2,
ABS(formula), as criteria_3,
SQRT(formula) as criteria_4
FROM subquery
WHERE criteria_1 = 0
ORDER BY criteria_2 , criteria_3 DESC, criteria_4, value_z)
"Remove (ID,SubID) duplicates while keeping the previous order"


I want to replace the last sentence (the one between quotes) by something, but GROUP BY ID, SubID just randomly picks any (ID, SubID) value.
You possibly need something along the lines of groupwise max.