Re: finding last date

On Wed, 23 May 2007 11:14:57 -0700, Robert Hicks 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

WITH MISHMASH AS (
   SELECT NAME,VOY,SOME_DATE,
          MAX(SOME_DATE) OVER ( PARTITION BY NAME) AS LAST_DATE    FROM TABLE )
select NAME,VOY,SOME_DATE
from MISHMASH
where SOME_DATE=LAST_DATE;

If the "VOY" column is not required, then the query is much simpler:

select name,max(some_date)
from table
group by name;

This is really elementary SQL. Please, do us all a favor and sto using this group as a free CBT. It's annoying as heck and denigrating to the people like me who did invest the time and effort to learn things.    

-- 
http://www.mladen-gogala.com