How to select the latest records for distinct column values?

Question

In my database I have profile ids with statuses which also have a timestamp. I want to have the latest status for each profile using a query. How can I do this the easiest way?

So f.e. for:

Id      Profile  Timestamp
-------------------------
1        1       1550588089
2        1       1550588099
3        3       1550588183
4        4       1550588333
5        4       1550588534
6        4       1550588377

I want to have

Id      Timestamp
-------------------------
2       1550588099
3       1550588183
5       1550588534

Fahmi · Accepted Answer · 2019-02-20T08:21:01.190

1

You can use correlated subquery

DEMO

select id as status, Timestamp
from tablename a where timestamp in (select max(timestamp) from tablename b where a.profile=b.profile )

OUTPUT:

tatus   Timestamps
2       1550588099
3       1550588183
5       1550588534

OR you can use row_number()

select * from 
(
select *, row_number() over(partition by profile order by timestamp desc) as rn
from tablename
)A where rn=1

edited Feb 20 '19 at 08:21

answered Feb 20 '19 at 07:54

Fahmi

37,315
5
22
31

Does the first query work? Isn't it right that `Max` just gives one result row? – Klyner Feb 20 '19 at 08:14
@Klyner, have you tried it? it should definitely work – Fahmi Feb 20 '19 at 08:14
1

But you can remove the subquery's GROUP BY. – jarlh Feb 20 '19 at 08:20
@Klyner, you can check here -https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=ca0ee1890d8c25689afd33da918b2399 – Fahmi Feb 20 '19 at 08:21
1

Thanks a lot for the demo's! It does :) – Klyner Feb 20 '19 at 08:48

score 1 · Answer 2 · answered Feb 20 '19 at 07:56

1

use row_number() which support maximum dbms

 select * from 
(
 select *,row_number() over(partition by Id order by timestamp desc) rn
 from table
) t where t.rn=1

answered Feb 20 '19 at 07:56

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 3 · answered Feb 20 '19 at 08:33

This query:

select profile, max(timestamp) maxtimestamp 
from tablename
group by profile

returns all the maximum timestamps for each profile.
So you get what you need by joining it to the main table:

select id, timestamp
from tablename t
inner join (
  select profile, max(timestamp) maxtimestamp 
  from tablename
  group by profile 
) g
on g.profile = t.profile and g.maxtimestamp = t.timestamp

See the demo

How to select the latest records for distinct column values?

3 Answers3