Link to home
Create AccountLog in
Avatar of Sthokala
SthokalaFlag for United States of America

asked on

get latest record from db

Hi,
  I have  a data in table like below
id      Date

21      2012-05-01 00:00:00.000
20      2012-05-01 00:00:00.000
21      2012-04-26 00:00:00.000
4      2012-07-02 00:00:00.000

I want to get data like below
21      2012-05-01 00:00:00.000
20      2012-05-01 00:00:00.000
4      2012-07-02 00:00:00.000

for id 21, the dates are different. I want to get the record with latest date. Please let me know how can I do it.

Thank you,
Avatar of lietaer
lietaer
Flag of France image

hello,

SELECT DISTINCT id
    FROM yourtable t1
    WHERE datetime = (SELECT MAX(t2.datetime) FROM yourtable t2 where t2.id = t1.id)
Avatar of Qlemo
The "contemporary" way to do it is by using windowing/ranking. That is necessary if the simple check for max date per ID is not sufficient, e.g. not unique, or you need more criteria to check, or you want more than one row per ID:
select * from 
(select *, rn = row_number() over (partition on id order by "date" desc) data
where rn = 1;

Open in new window

Avatar of Sthokala

ASKER

Hi Lietaer,
    thank you for your response. By executing the query it is displaying only one row which has id 4.

Please let me know how can I do it.

Thank you
ASKER CERTIFIED SOLUTION
Avatar of lietaer
lietaer
Flag of France image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I agree, the query shown should give you correct results. Though I would rather use something like:
select t1.*
from tbl t1 join (select id, maxdate = max("date") from tbl group by id) maxt
on t1.id = maxt.id and t1."date" = maxdate

Open in new window

because it is more flexible.
BTW, my other SQL is missing some lines:
select * from 
(select *, rn = row_number() over (partition on id order by "date" desc)
 from tbl)  data
where rn = 1;

Open in new window