Re: Help w/ this Query...
This should work,
SELECT case_no, dis_date, coll_agency
FROM case_dispos a
WHERE dis_date = (SELECT MAX(dis_date) FROM case_dispos b WHERE a.case_no= b.case_no);
DAS
--
David Snyder @ Towers Perrin - Philadelphia, PA
EMAIL: (h) dave@snide.com (w) snyded@towers.com
WEB: http://www.ece.vill.edu/~dave/ (Now NETSCAPE enhanced!)
Hannah Sobie Fiabema <hfiabema@osf1.gmu.edu> wrote in article
<57786n$vlj@portal.gmu.edu>...
> Gurus,
> I have data that looks like this:
>
>
> case # date
> ------- ----
>
> 913-01234 01/01/95
> 913-01234 02/02/95
> 913-01234 03/03/96
>
> . .
> . .
> . .
>
>
> I am writing a report and for each instance of the case number I
> just need to retrieve the LAST case w/ the most recent date. In this
> example, I WOULD ONLY WANT TO PRINT - 913-01234 03/03/96, ok.
>
> This is my problem query:
>
>
>
> SELECT
> unique(case_no),
> max (dis_date),
> coll_agency
> FROM case_dispos
> WHERE (dis_date between "01/01/88" AND today)
> AND disposition = "RF"
> AND authr = "CA"
> group by case_no, coll_agency
> order by coll_agency
>
> Thnks in advance.
>
>