get latest(date) row of groups

Question

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..

This appears to be a duplicate of http://stackoverflow.com/q/12102200/1324815. Please note, I would strongly recommend using the [left outer join answer](http://stackoverflow.com/a/28090544/1324815) instead of the selected answer on that post. With correct indexing, it almost always more efficient. — Chad Capra, Jul 15 '16 at 04:12

score 0 · Accepted Answer · answered Jul 14 '16 at 23:39

0

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);

answered Jul 14 '16 at 23:39

Gordon Linoff

1,242,037
58
646
786

thank you :) I did't tought that it would be that easy - lesson learned :) – m. blend Jul 15 '16 at 00:02

score 0 · Answer 2 · answered Jul 15 '16 at 00:09

0

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

answered Jul 15 '16 at 00:09

Muthaiah PL

1,048
3
15
26

get latest(date) row of groups

2 Answers2