Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: finding last date

Re: finding last date

From: <fitzjarrell_at_cox.net>
Date: 23 May 2007 11:28:39 -0700
Message-ID: <1179944919.428512.116990@q66g2000hsg.googlegroups.com>


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 Received on Wed May 23 2007 - 13:28:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US