0

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.

  • I'm not sure I understand what defines the order of rows. Could you please elaborate on that? If your example had actual values and explained which row should be returned, that would be great. – Andriy M Jun 20 '16 at 3:35
  • The outer SELECT does nothing useful (in your EDIT). – Rick James Jul 1 '16 at 3:50
0

You possibly need something along the lines of groupwise max.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.