How to sort within a group of data and sort the groups by max value within each group in a MySQL query.

Question

I have a dataset that looks like so:

id  entry_id  the_data
1      100       Z
2      300       B
3      100       C
4      200       F
5      300       Q
6      300       A
7      300       L
8      100       N
9      200       O

The results should give only the 3 most recent records (by id) for each corresponding entry ( by entry_id), they need to be grouped together (by entry_id), sorted descending (by id), and (the tricky part) the grouped sets need to be sorted descending by the most recent record (by highest id within the subset), next highest within the next subset, and so on.

So, the desired results should look like this:

id  entry_id  the_data
9      200       O
4      200       F
8      100       N
3      100       C
1      100       Z
7      300       L
6      300       A
5      300       Q

This query gets close, but fails to sort the groupings of records by max id within each group

select t1.id, t1.entry_id, t1.the_data
from mytable t1
where (
    select count(t2.id) from mytable t2
    where t2.entry_id = t1.entry_id and t2.id > t1.id
) <= 2
order by t1.entry_id desc, t1.id desc

the results look like this:

id  entry_id  the_data
7      300       L
6      300       A
5      300       Q
9      200       O
4      200       F
8      100       N
3      100       C
1      100       Z

How can this query be accomplished to give the desired results above?

Answer 1

Introduce a subquery that gets the max id for each entry_id grouping, join and sort and you're done...

select t1.id, t1.entry_id, t1.the_data
from mytable t1
inner join (
    select entry_id, MAX(id) AS maxid
    from mytable
    group by entry_id
) maxs
on maxs.entry_id = t1.entry_id
where (
    select count(t2.id) from mytable t2
    where t2.entry_id = t1.entry_id and t2.id > t1.id
) <= 2
order by maxs.maxid desc, t1.entry_id desc, t1.id desc