1

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

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Cristian A. Hurtado Romero
  • 131
  • 1
  • 4
  • 10

1 Answers1

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
Robert
  • 25,425
  • 8
  • 67
  • 81
  • That works great!! Thank you so much!! If I want to query over the resulting dataset how could I achieve this? I'm trying using **SELECT * FROM (-YOUR ANSWER-) AS RES** But it throws a syntax error at the beginning – Cristian A. Hurtado Romero Mar 07 '13 at 23:18
  • This is what im doing **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') ) The error shown is _Could not execute statement. Syntax error near ')'. SQLCODE=-131, ODBC 3 State="42000"_ – Cristian A. Hurtado Romero Mar 08 '13 at 14:17
  • I added one more solution, try it please. – Robert Mar 08 '13 at 14:41
  • Thanks!! I tried your solution and also added this 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 (id,ref,status,date). Apparently it needs a columns specification when giving an alias. Anyway thank you so much!! – Cristian A. Hurtado Romero Mar 08 '13 at 14:56
  • @CristianAlejandroHurtadoRom I'm glad I could help you. Don't forget you can vote for the answer :) – Robert Mar 08 '13 at 15:09