6

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?

Gustav Bertram
  • 14,121
  • 3
  • 41
  • 65
ina
  • 18,561
  • 37
  • 117
  • 197

3 Answers3

25

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
The Scrum Meister
  • 29,113
  • 8
  • 64
  • 63
  • 1
    Exactly what I needed with some slight alterations - Thanks! – mmrtnt Jun 14 '13 at 15:59
  • Is it necessary to have the WHERE clause repeated twice? – ina Apr 06 '15 at 03:26
  • It's working well when you have records updated at different times. But suppose we have all records updated at the same (i.e. due to data import) all records coming in the list. I'm not sure now, how can I remove the duplicates ones. – Lalit Kumar Maurya May 28 '22 at 15:37
6
SELECT * FROM 
(SELECT * FROM table WHERE uuid='bla' ORDER BY datetime desc) table 
GROUP BY version;
bummi
  • 26,839
  • 13
  • 60
  • 97
bryan
  • 71
  • 1
  • 1
2

There is a better way for me, you could add a desc to group by:

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

why it works is because my id's are generated and so always the latest id means the latest datetime

azerafati
  • 17,397
  • 7
  • 67
  • 69