8

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,591
  • 3
  • 40
  • 65
ina
  • 19,167
  • 39
  • 122
  • 201

4 Answers4

26

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,681
  • 8
  • 66
  • 64
  • 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
  • 27,123
  • 14
  • 62
  • 101
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
  • 18,215
  • 7
  • 67
  • 72
0

I used a separate method on php to get the id's and put it into an array. Then on another sql query i searched the data by looking up the id's

$ids  = array();

$sql = "SELECT pid FROM table_name  WHERE DATE(column_name)='yyyy-mm-dd'  ORDER BY column_name ASC";

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {

if (!in_array($row['value'], $ids)) {
    array_push($ids, $row['value']);
}
    }
}

And then

$sql = "SELECT * FROM table_name WHERE id IN (".implode(',', $ids).") ORDER BY column_name ASC";
Dinesh M
  • 1
  • 1