Re: Complex PL-SQL Query ?

Damjan,

Your solution is simple and straightforward, based squarely on the problem statement. However (given the OP's propensity for concealing vital parts of the problem) I am confidently expecting another post explaining that these are not the only 4 columns, there are additional columns which need to be displayed but cannot be GROUPed BY. ;-)

Damjan S. Vujnovic wrote:

>"Fred Zimmerman" <silversw2000_at_yahoo.com> 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 hope that there's an error above(YDT instead of YTD), or elese there's no
>use reading further.
>
>
>
>>I want to return the following:
>>
>>
>
>It is hardly possible to draw such a conclusion from your original post...
>
>
>
>>YOS 33 700012/S 10-JUL-02
>>YOGN 22 669911/T 12-JUL-01
>>YTD 5 443322/B 28-NOV-02
>>
>>
>
>How about:
>
>SELECT hulltype, hullno, location, MAX(asofdate)
>FROM cimshist
>GROUP BY hulltype, hullno, location
>
>
>
>>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:
>>
>>
>>>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?
>>>>
>>>>
>>>>
>>>>
>
>Any particular reason why your table isn't normalised? Reconsider your table
>design, if possible. If CIMSHIST is a view created by joining some tables,
>then you should query the original table(s) because of performance (GROUP BY
>clause will be simpler).
>
>Regards,
>Damjan S. Vujnovic
>
>University of Belgrade
>School of Electrical Engineering
>Department of Computer Engineering & Informatics
>Belgrade, Yugoslavia
>
>http://galeb.etf.bg.ac.yu/~damjan/c
>
>
>
>
Received on Wed Nov 20 2002 - 15:25:02 CST