Re: Complex PL-SQL Query ?

> YDT 5 443322/B 01-JAN-99
> YTD 5 443322/B 28-NOV-02
Should the first be 'YTD'? If yes, you can do something like this:

select * from <table> t1 where exists ( select * from

        (select HULLTYPE, max(ASOFDATE) d1 from <table> group by ASOFDATE) t2
         where t1.HULLTYPE=t2.HULLTYPE and t1.ASOFDATE=t2.d1);

• Jusung Yang

silversw2000_at_yahoo.com (Fred Zimmerman) wrote in message news:<a695a242.0211200558.fea1ce3_at_posting.google.com>...
> Martin,
>
> This does not return all the records. Let me give you more complete
> picture (still a subset of the table's data).
>
> HULLTYPE HULLNO LOCATION ASOFDATE
> -------- ------ -------- --------
> YOS 33 700012/S 13-APR-95
> YOS 33 700012/S 05-MAY-99
> YOS 33 700012/S 10-JUL-02
> YOGN 33 700012/S 13-APR-95
> YOGN 22 669911/T 15-JUN-99
> YOGN 22 669911/T 12-JUL-01
> YDT 5 443322/B 01-JAN-99
> YTD 5 443322/B 28-NOV-02
>
> I want to return the following:
>
> YOS 33 700012/S 10-JUL-02
> YOGN 22 669911/T 12-JUL-01
> YTD 5 443322/B 28-NOV-02
>
> The query you gave me only returns:
>
> YTD 5 443322/B 28-NOV-02
>
> (Latest date (Max)).
>
> Fred Z
>
> Martin Doherty <martin.doherty_at_elcaro.moc> wrote in message news:<ahyC9.10$cK2.222_at_news.oracle.com>...
> > Fred,
> >
> > You didn't specify your version of Oracle.
> >
> > Here's a simple query that should work in most versions. (I'm assuming
> > that ASOFDATE is of DATE data type).
> >
> > select * from cimshist
> > where asofdate = (select max(asofdate) from cimshist)
> >
> > hth
> > Martin Doherty
> >
> > Fred Zimmerman wrote:
> >
> > >I have a table CIMSHIST that has fields: hullno, hulltype, location, asofdate.
> > >
> > >I can have some data like the following
> > >
> > >HULLTYPE HULLNO LOCATION ASOFDATE
> > >-------- ------ -------- --------
> > >YOS 33 700012/S 13-APR-95
> > >YOS 33 700012/S 05-MAY-99
> > >YOS 33 700012/S 10-JUL-02
> > >
> > >
> > >I only want to return the record with
> > >latest ASOFDATE.
> > >
> > >Can anyone give me the SQL query to handle
> > >this task?
> > >
> > >
Received on Wed Nov 20 2002 - 14:07:03 CST