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!
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.