Re: finding last date

On May 23, 2:28 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On May 23, 1: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
>
> First if DATE is an actual column name you need to change it since
> DATE is a datatype and a reserved word in Oracle. Second, if you
> can't use GROUP BY to get these results you should probably take a
> refresher course in SQL. This is a simple query to write:
>
> select name, voy, mydate
> from mytable
> where (name, mydate) in (select name, max(mydate) from mytable group
> by name);
>
> I presume since your example is oversimplified the query I just
> supplied won't work without severe modification. Such is the price
> you pay for not providing accurate details regarding what you are
> trying to do.
>
> David Fitzjarrell

Those are simplified column names is all and it isn't actually DATE. I was just trying to simplify the explanation. The column names are actually LLY_NUM, VOY_NUM, TP_DATE.

I will try what you have.

Robert Received on Wed May 23 2007 - 14:11:45 CDT