Re: finding last date

On 23 May 2007 11:14:57 -0700, Robert Hicks <sigzero_at_gmail.com> wrote:

>I have a table (simplified):
>
>NAM E VOY DATE
>
>
>The NAME column can have the same entry up to 10 times based on the
>VOY. So it could look something like this:
>
>NAME VOY DATE
>123456 0 <some date>
>123456 1 <some date>
>345677 0 <some date>
>345677 1 <some date>
>345677 2 <some date>
>345677 3 <some date>
>098766 0 <some date>
>
>Some of the NAME items have VOYS up to 9 but not all of them do.
>
>I need to pull out the NAME and the LAST <some date> for each
>"distinct" NAME. So in the above case I would get:
>
>123456 1 <some date>
>345677 3 <some date>
>098766 0 <some date>
>
>The <some date> is always later as the VOY increments up.
>
>Any help would be appreciated. I have been banging my head all day.
>
>Robert

select name, voy,date
from foo f1
where date =
(select max(date)
 from foo f2
 where f2.name = f1.name
)

You don't want to know how often this question have been asked. Banging should be replaced by a compusory search of the archives.

-- 
Sybrand Bakker
Senior Oracle DBA