eric eric - 5 months ago 18
SQL Question

Group by id and select most recent

I have a table example like this:

date id status
01/01/2013 55555 high
01/01/2014 55555 low
01/01/2010 44444 high
01/01/2011 33333 low


I need in order:
group by id
and select most recent date.

this is the result I want.

date id status
01/01/2014 55555 low
01/01/2010 44444 high
01/01/2011 33333 low


I do not care the order of the rows.

Answer Source

you need to join your table with a subquery that "links" the record date with the greatest date for each id:

select a.*
from your_table as a
     inner join (
         select id, max(date) as max_date 
         from your_table 
         group by id
     ) as b on a.id = b.id and a.date = b.max_date;