Re: finding last date

From: ErikYkema <erik.ykema_at_gmail.com>
Date: 23 May 2007 11:31:39 -0700
Message-ID: <1179945099.424135.12600@q19g2000prn.googlegroups.com>

On May 23, 8:14 pm, Robert Hicks <sigz..._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

Hi Robert,
What about

select name
, max(date) as max_date
from table_name_voy_date
group by name

another one could be:

select t2.name, t2.date
from (select name, max(voy) as max_voy from table_name_voy_date group by name) t1
join table_name_voy_date t2
on t1.name = t2.name
and t1.max_voy = t2.voy

Please be aware that date is a reserved oracle word, so better name it something different.

If this helps, I recommend you buy a good book on basic SQL. If not, give us some more clues about what the challenge is. Regards,
Erik Ykema Received on Wed May 23 2007 - 13:31:39 CDT