Re: Complex PL-SQL Query ?

On 20 Nov 2002 05:58:08 -0800, silversw2000_at_yahoo.com (Fred Zimmerman) wrote:

>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?
>> >
>> >

Here you go...

select * from CIMSHIST a
where asofdate = (select max(asofdate) from CIMSHIST b where a.hulltype = b.hulltype) Received on Wed Nov 20 2002 - 12:52:34 CST