ina ina - 2 months ago 19
MySQL Question

MySQL - Group by with Order by DESC

table: uuid, version, datetime

version is not unique, but the idea is to fetch only the rows with the latest datetime for a given uuid

SELECT * FROM table WHERE uuid='bla' GROUP BY version ORDER BY datetime desc


... of course gets datetime asc results -- is there a way to "preorder" the group by to desc, so that only the latest version is fetched?

Answer

since the table only has those 3 field, and you are filtering by uid you can just use the MAX without the JOIN:

SELECT version, MAX(datetime) Maxdatetime
FROM table
WHERE uuid='bla'
GROUP BY version

However, if the table had more fields, or you are not filtering by uid - you need to first get the MAX datetime for each version, then select the row:

SELECT t.uuid, t.version, t.datetime 
FROM table t JOIN (
    SELECT version, MAX(datetime) Maxdatetime
    FROM table
    WHERE uuid='bla'
    GROUP BY version
) r ON t.version = r.version AND t.datetime = r.Maxdatetime
WHERE t.uuid='bla'
ORDER BY t.datetime desc