Re: Complex PL-SQL Query ?

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 - 07:58:08 CST