user3746480 user3746480 - 1 year ago 157
MySQL Question

Select top rows from each group - MySql

I have to retrieve the top rows from each group, based on the max values of two columns.

This is the table:

tag_series_id | tag_season_num | tag_episode_num
269653 2 24
269653 3 1
269653 3 2
269653 3 3
281593 1 9
281593 1 11
281593 1 10

The result set should be the record with the maximum
should be higher priority.

The result set should be this:

tag_series_id | tag_season_num | tag_episode_num
269653 3 3
281593 1 11

These are some of my failed attempts:

SELECT tag_series_id, tag_season_num, tag_episode_num, tag_watch_status
FROM taggedshows WHERE
GROUP BY tag_series_id, tag_season_num
ORDER BY tag_season_num desc, tag_episode_num desc;

SELECT tag_series_id, tag_season_num, tag_episode_num
FROM (SELECT * from taggedshows
ORDER BY tag_series_id, tag_season_num desc, tag_episode_num desc) x
GROUP BY tag_series_id;

SELECT tag_series_id, MAX(tag_season_num), MAX(tag_episode_num)
FROM taggedshows
GROUP BY tag_series_id;

Answer Source

Is a bit nested but i think you need two level subquery

select * from taggedshows
where (tag_season_num, tag_episode_num)  in 
    (select tag_season_num, max(tag_episode_num)  from  taggedshows
    where ( tag_series_id , tag_season_num) in 
    (select tag_series_id max(tag_season_num)  from taggedshows
    group by tag_seried_id)
    group by tag_season_num)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download