CODEWITHSUNDEEP
mysql
mdnba50
mdnba50

Reputation: 379

MySql get highest count per parent ID

ID    item_ID    parent_ID   count
================================
1      11          2          5
2      12          2          6
3      13          3          2
4      14          3          3
5      15          2          7
6      16          1          3

SELECT * FROM relations ORDER BY count DESC

The row that should be returns are 2,4 and 6 because they have the highest count for their parent_ID

how do i change the query to accomplish this?

Upvotes: 0

Views: 79

Answers (1)

juergen d
juergen d

Reputation: 204894

The inner select gets the highest count for each parent_ID. If you join against that, it filters out the relevant records

select t1.*
from your_table t1
join
(
    select parent_ID, max(count) as mcount
    from your_table
    group by parent_ID
) t2 on t1.parent_ID = t2.parent_ID
    and t1.count = t2.mcount

Upvotes: 1

Related Questions