CODEWITHSUNDEEP
phpmysqldatabasegroup-by
Skylineman
Skylineman

Reputation: 576

MySQL Group By + Order

I've got a problem with grouping my rows.

Example Table

ID, GroupID, INFO, COUNTER
1, 123456, INFO, 21
2, 654321, INFO, 20
3, 123456, INFO, 30
4, 654321, INFO, 11

First of all, I'd like to display them with this:

SELECT * 
FROM table 
GROUP BY GroupID 
ORDER BY COUNTER DESC 
LIMIT 0, 60

So it should display only one of group type. It does, but the order is not good. So I think it's not getting the right number of counter paired to the GroupID.

The right displayed result would be: (the main order selector has to be the count)

ID, GroupID, INFO, COUNTER
3, 123456, INFO, 30
2, 654321, INFO, 20

How should I solve?

Upvotes: 0

Views: 97

Answers (1)

darma
darma

Reputation: 4747

    SELECT tablename.* FROM tablename 
    WHERE tablename.COUNTER = 
(SELECT MAX(COUNTER) FROM tablename AS f WHERE f.GroupID = tablename.GroupID) 
    ORDER BY tablename.COUNTER DESC

Edited : This will get you the complete rows containing the max value of COUNTER for each GroupID, and order the final results by COUNTER desc.

Upvotes: 1

Related Questions