Re: Complex PL-SQL Query ?

"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 Received on Wed Nov 20 2002 - 11:28:06 CST