Sybase get last record of record group

Question

I have a table like this

id     ref    status    date

1     150     P     10/01/2010
2     150     P     11/01/2010
3     150     P     12/01/2010
4     151     P     10/01/2010
5     151     C     NULL
6     152     P     11/01/2010
7     152     P     12/01/2010
8     152     C     NULL

And what i want is to retrieve all the records that either have a status equals to C and (for those who have the status P) the last record according to the column date.

For example:

id     ref    status    date

3     150     P     12/01/2010
5     151     C     NULL
8     152     C     NULL

So far I have tried to do subquerys but i dont get to have the last record according to date.

I´m using Sybase 8.0.2.4542. Thank you so much guys!!

Answer 1

Try to use the solution:

select id, ref, status, max(date)
from table 
where status = 'P'
group by id, ref, status, date
union all
select id, ref, status, date
from table 
where status = 'C'

with one query:

select *from 
(select id, ref, status, max(date)
from table 
where status = 'P'
group by id, ref, status, date
union all
select id, ref, status, date
from table 
where status = 'C') RES