Alan Alan - 7 months ago 11
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

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);
Comments