MySQL: How to get n latest rows of a distinct type

Question

Putting this as simply as I can, I have the following table structure:

Date | Type | Title

Say Type is a value in the range 1-10, I have 1,000s of records in the table, and I want the 5 most recent records of unique type. So the result would be something like:

             Date | Type | Title
2009-06-04 14:32:00 | 4 | Zeppo
2009-06-04 14:31:00 | 2 | Groucho
2009-06-04 14:30:00 | 8 | Harpo
2009-06-04 14:29:00 | 5 | Gummo
2009-06-04 14:28:00 | 3 | Chico

Seems like I either want DISTINCT to only apply to the Type column, or I want a GROUP BY which will apply after an ORDER BY clause.

All in MySQL 4.

Answer 1

Am I missing something? The easy solution seems to be:

SELECT  MAX(date) AS max_date, type, title
FROM    table
GROUP BY
        type  
ORDER BY
        max_date DESC
LIMIT 5

And it should be extremely fast.

Answer 2

Didn't test in for MySQL 4, but in MySQL 5 this can be easily done.

You'll need to have some kind of a PRIMARY KEY in your table for this to work.

SELECT  l.*
FROM    (
        SELECT  type,
                COALESCE(
                (
                SELECT  id
                FROM    mytable li
                WHERE   li.type= dlo.type
                ORDER BY
                        li.type DESC, li.date DESC, li.id DESC
                LIMIT 4, 1
                ), CAST(0xFFFFFFFF AS DECIMAL)) AS mid
                COALESCE(
                (
                SELECT  date
                FROM    mytable li
                WHERE   li.type= dlo.type
                ORDER BY
                        li.type DESC, li.date DESC, li.id DESC
                LIMIT 4, 1
                ), '9999-31-12') AS mdate
        FROM    (
                SELECT  DISTINCT type
                FROM    t_mytable dl
                ) dlo
        ) lo, t_mytable l
WHERE   l.type >= lo.type
        AND l.type  <= lo.type
        AND (l.date, l.id) >= (lo.mdate, lo.mid)

See this entry in my blog for more detail on how it works:

• Advanced row sampling - how to select N rows for a GROUP in MySQL.

If you cannot add a PRIMARY KEY to implement this solution, you may try using less efficient one using system variables:

SELECT  l.*
FROM    (
        SELECT  @lim := 5,
                @cg := -1
        ) vars,
        mytable l
WHERE   CASE WHEN @cg <> type THEN @r := @lim ELSE 1 END > 0
        AND (@r := @r - 1) >= 0
        AND (@cg := type) IS NOT NULL
ORDER BY
        type DESC, date DESC

It's described here:

• Row sampling

Update:

If you don't want to select 5 records for each type (which would give 5 x number of types records in the resultset), but instead want to select 5 latest records with distinct type (which would give 5 records in the resultset), use this query:

SELECT  date, type, title
FROM    mytable m
WHERE   NOT EXISTS
        (
        SELECT  1
        FROM    mytable mi
        WHERE   mi.date > m.date
                AND mi.type = m.type
        )
ORDER BY
        date DESC
LIMIT 5

If you have lots of types, this will be more efficient that using GROUP BY, provided you have an index on date.