Re: SQL: find all latest value = 'X'

Jeff Bangle wrote:
>
> 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?

There must be a dozen ways of writing this. My preference is for the most "standard", viz:

select * from rating_table
where (personid,ratingdate) in
(select personid,max(ratingdate)
 from rating_table

 group by personid)	-- get max dates for each person
and rating = 'A'	-- check rating

With an index on (personid,ratingdate) the subquery is evaluated wholly in the index and the outer query block is also a fully indexed query.

HTH

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"