Re: finding last date

On May 23, 2:28 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On May 23, 1:14 pm, Robert Hicks <sigz...@xxxxxxxxx> 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

.

Relevant Pages

  • Re: Damn you, FEDEX! or Nikon D40 lost in Springfield, MO blackhole.
    ... the 2 mp Mavica he had been using with a Nikon D40. ... After shopping around, he got me to order one for him. ... The shipper had it insured, but from what I have read it could take weeks to sort this crap out. ... You may get your insurance from FedEx and a couple weeks later they find it and deliver it. ...
    (alt.photography)
  • Re: Happy Valentines Day
    ... Uncle Al ways, "The difference between seduction and rape is ... salesmanship (e.g., a box of chocolates)." ...
    (sci.physics)
  • Re: Happy Valentines Day
    ... Uncle Al ways, "The difference between seduction and rape is ... salesmanship (e.g., a box of chocolates)." ...
    (sci.physics.relativity)
  • Re: The Sci-Fi Rejection Letter That Time Forgot
    ... nations have stockpiled arsenals of these incredible bombs and the time the story is set. ...
    (rec.arts.sf.written)
  • RE: copied music cds have a skip in last 18 seconds
    ... If installing all missing Windows Updates doesn't fix your problem ... xiowan.......in tucson ...
    (microsoft.public.windows.mediacenter)