0
ID | serial | created_at | data 1..45
1  | 001    | timestamp  |  ....
2  | 002    | timestamp  |  ....
3  | 003    | timestamp  |  ....
4  | 001    | timestamp  |  ....
5  | 002    | timestamp  |  ....
6  | 001    | timestamp  |  ....
7  | 003    | timestamp  |  ....
8  | 004    | timestamp  |  ....
9  | 001    | timestamp  |  ....

i expect a result of IDs 9,8,7,5 as i need the latest row of each serial

=> "SELECT * FROM `rig_stats` GROUP BY `rig_stats`.`serial` ORDER BY `rig_stats`.`id` DESC"

this however gives me the first occurence, order desc by id so the result is 1 2 3 8 and is not what i wanted.

using mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64)

Tim Kretschmer
  • 2,272
  • 1
  • 22
  • 35

1 Answers1

0

You may join to a subquery which finds the latest row for each serial:

SELECT s1.*
FROM serial s1
INNER JOIN
(
    SELECT serial, MAX(created_at) AS max_created_at
    FROM serial
    GROUP BY serial
) s2
    ON s1.serial = s2.serial AND
       s1.created_at = s2.max_created_at;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360