Geek Answers Handbook

MySQL - ordering team from DESC

table: uuid, version, datetime

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

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

... of course, it gets the result with the date per day - is there a way to “pre-arrange” the group with desc, so that only the latest version will be downloaded?

+5
source share
3 answers

since the table contains only these 3 fields and you are filtering with uid, you can just use MAX without JOIN:

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

However, if there were more fields in the table or you do not filter by uid- you need to first get the MAX datetime for each version, and 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
+21
SELECT * FROM 
(SELECT * FROM table WHERE uuid='bla' ORDER BY datetime desc) table 
GROUP BY version;
+4

There is a better way for me, you can add descto the group:

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

why it works, because my id is generated, and therefore always the last identifier means the latest datetime

+2
source

More articles:


All Articles