Retrieve and entry for all "type"s but only the most recent entry for each "type" from MySQL table

Question

Basically if I had a mysql table with columns: id, type, content, version.

There will be multiple entries with the same type. Version is a number which increments with each new entry of a certain type.

I want to be able to get the most recent entry of each type for all types with one query.

Is this possible or do I need to do separate queries for each type?

Cheers

Answer 1

SELECT  mi.*
FROM    (
        SELECT  DISTINCT type
        FROM    mytable
        ) md
JOIN    mytable mi
ON      mi.id = 
        (
        SELECT  mo.id
        FROM    mytable mo
        WHERE   mo.type = md.type
        ORDER BY
                mo.type DESC, mo.version DESC, mo.id DESC
        LIMIT 1
        )

Create an index on (type, version, id) for this to work fast.

Answer 2

try:

SELECT
    y.*
    FROM YourTable y
        INNER JOIN (SELECT
                        Type,MAX(Version) AS MaxVersion
                        FROM YourTable
                        GROUP BY Type
                   ) dt ON y.Type=dt.Type AND y.Version=dt.Version