select personid from rating_table ra
where ra.rating = 'A'
and not exists
(select null from rating_table rr
where rr.personid = ra.personid
and rr.ratingdate > ra.ratingdate)
--
------------
Kind reGards
\ / |
X |
/ \ x
Gerard
Jeff Bangle <JBangle_at_erols.com> schreef in artikel
<34418EE6.67758DBB_at_erols.com>...
> 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 Mon Oct 13 1997 - 00:00:00 CDT
