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

From: replace this with _at_ <_at_)xs4all.nl>
Date: 1997/10/14
Message-ID: <34428921.3403954@news.xs4all.nl>#1/1

On Sun, 12 Oct 1997 23:00:55 -0400, Jeff Bangle <JBangle_at_erols.com> wrote:

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

How about this:

SELECT	A.PERSONID
,	A.RATINGDATE
FROM	RATING_TABLE	A
WHERE	A.RATINGDATE =
	(SELECT	MAX(RATINGDATE)
	FROM		RATING_TABLE	B
	WHERE		B.RATING = 'A'
	AND		A.PERSONID = B.PERSONID)
-------------------------------------------------------------------------------
--- Martin Jesterhoudt                      

--- martinj(replace this by @)xs4all.nl

---

Received on Tue Oct 14 1997 - 00:00:00 CDT