Re: Complex PL-SQL Query ?

From: Chan Sanghera <ChanSaghera_at_tarans.net>
Date: 3 Dec 2002 02:02:55 -0800
Message-ID: <6a566d3d.0212030202.338ac4c5@posting.google.com>

Hi Fred,

Is there another mistake in the data, line 4 has HULLNO 33 & LOCATION of 700012/S instead of 22 & 669911/T, the query

select HULLTYPE,HULLNO ,LOCATION,max(ASOFDATE) from <table>
group by HULLTYPE,HULLNO ,LOCATION

will return :-

HULLTYPE HULLNO LOCATION MAX(ASOFD

---------- ---------- ---------- ---------
YOGN               22 669911/T   12-JUL-01
YOGN               33 700012/S   13-APR-95
YOS                33 700012/S   10-JUL-02
YTD                 5 443322/B   28-NOV-02

If there is no mistake then use

select t1.HULLTYPE, t1.HULLNO, t1.LOCATION, t1.ASOFDATE from google t1,

    (select t2.hulltype, max(t2.asofdate) maxdate

     from google t2
     group by t2.hulltype) t3

where t1.HULLTYPE = t3.hulltype
and t1.asofdate = t3.maxdate

or the corrected

select * from google t1 where exists ( select * from

         (select HULLTYPE, max(ASOFDATE) d1 from google group by HULLTYPE) t2

          where t1.HULLTYPE=t2.HULLTYPE and t1.ASOFDATE=t2.d1);

regards Chan.

damjan_at_galeb.etf.bg.ac.yu (Damjan S. Vujnovic) wrote in message news:<93c520ee.0211200928.2bce8310_at_posting.google.com>...
> "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 Tue Dec 03 2002 - 04:02:55 CST