Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> SQL: find all latest value = 'X'
I have table with three pertinent columns:
personid rating ratingdate
======== ====== ==========
01000101 B 10-JAN-97 12304956 22-NOV-96 10203049 A 15-DEC-96 01000101 A 10-JUL-96
I need to find all personid's where the most recent rating for that person was 'A'. Null ratings don't count. I've come up with a query that seems to work, but it looks messy:
select personid, ratingdate
from rating_table
where rating = 'A'
intersect
select personid, max(ratingdate)
from rating_table
where rating is not null
group by personid
Is there a better way? Is my query even working the way I think it is? Received on Sun Oct 12 1997 - 00:00:00 CDT