How to get the row with max timestamp in postgres?
22,449
I would simply go for...
SELECT usr, event_dt FROM mytable ORDER BY event_dt DESC LIMIT 1
Author by
RoundPi
Updated on February 02, 2020Comments
-
RoundPi about 4 years
I need to get a row of information with max timestamp in postgresql. Below is a demo for this question:
drop table Mytable cascade create table MyTable (usr char(1), event_dt timestamp without time zone); insert into mytable values ('A','01-JAN-2009 11:10:11'); insert into mytable values ('A','02-JAN-2009 11:10:22'); insert into mytable values ('B','02-JAN-2009 01:01:59' ); insert into mytable values ('C', '31-DEC-2008 02:02:02'); insert into mytable values ('D', '31-DEC-2008 03:03:03');
If I do
select max(event_dt) from ( select usr,event_dt from mytable where usr= 'A') as foo
It is sort of what I need but it only returns the event_dt "2009-01-02 11:10:22"
Where I want the usr as well sa event_dt from that row. How do I do it?
-
Torxed over 9 yearsTiming difference? I conducted a timing test on roughly one billion rows of data on both the OP and Najzero's solutions. There's aproxemately a 25% performance gain to doing Najzero's solution as well. So not only does it look clean but it's way faster (perhaps for obvious reasons)