m. blend m. blend - 4 months ago 8
SQL Question

get latest(date) row of groups

my problem is, that I stuck with this I guess simple problem.
Now 2 evenings Grrr...
I created a small example to keep it simple:

The source table looks like that: Some obj with some random status.
These statuses can be changed/updated by insert a new row.

Id | obj| status | date
---+----+--------+-----
1 | 1 | green | 2013
2 | 1 | green | 2014
3 | 1 | yellow | 2015
4 | 1 | orange | 2016 <- Last status of 1
5 | 2 | green | 2013
6 | 2 | green | 2014 <- Last status of 2
7 | 3 | green | 2010
8 | 3 | red | 2012 <- Last status of 3


I would need to get an output like that:

obj| status | date
---+--------+-----
1 | orange | 2016
2 | green | 2014
3 | red | 2012


text: The output shows the latest status of ech obj.

I hope somebody can help me..

Answer

A simple correlated subquery in the where clause does the trick:

select obj, status, date
from t
where t.date = (select max(t2.date) from t t2 where t2.status = t.status);