Alan Alan - 1 year ago 48
SQL Question

Query latest record by date and group by a serial column

I have data like this

--------------------------------
Date Serial State
2016-04-30 20:34:47 4 0
2016-04-30 20:34:48 5 0
2016-04-30 20:35:10 4 0
2016-04-30 20:35:08 5 1


I am trying to query the latest timestamp for each serial with it's associate state so it would be like this

Date Serial State
2016-04-30 20:35:10 4 0
2016-04-30 20:35:08 5 1


This seemed to grab me the latest date and group the serials but the state column doesnt change for some reason and stays at 0

Select MAX(date) as date, serial AS serial, state AS state
FROM testGraph
GROUP BY serial


Can someone help me out? Thanks

Answer Source

You can do this in the where clause:

select tg.*
from testgraph tg
where tg.date = (select max(tg2.date) from testgraph tg2 where tg2.serial = tg.serial);