Re: Anyone have a better way??
Date: 1997/07/15
Message-ID: <33CB46D6.757F_at_uk.sun.com>#1/1
Phil Mattson wrote:
>
> Hello all,
>
> I'm having a problem coming up with an optimal way to perform a certain
> query with an Oracle 7 DB. Anyone up to the task?
>
> Given:
>
> Oracle 7.3 DB
> A single table (ii.rdgtemp) has a dozen columns. The table is "ordered by
> dev ASC, quality DESC".
>
> An example of the data would be:
>
> dev quality owner misc1 misc2 ...
>
> 1 8 ABC .............
> 1 8 BCA .............
> 1 8 AAA .............
> 1 5 BBB .............
> 1 5 CCC .............
> 1 4 BAA .............
> 2 9 AAA .............
> 2 9 CCC .............
> 2 1 BCA .............
> 3 4 CCC .............
> 19 9 AAA .............
> 19 2 BBB .............
> 20 4 BCA .............
>
> The "Goal" is to retrieve a single highest quality row for each dev.
> If doesn't matter which "highest quality" row is selected for each dev.
>
> The select should return something like:
>
> dev quality owner misc1 misc2 ...
>
> 1 8 ABC .............
> 2 9 AAA .............
> 3 4 CCC .............
> 19 9 AAA .............
> 20 4 BCA .............
>
> It might also return something like (note a differnent row was used for dev
> 1):
>
> dev quality owner misc1 misc2 ...
>
> 1 8 BCA .............
> 2 9 AAA .............
> 3 4 CCC .............
> 19 9 AAA .............
> 20 4 BCA .............
>
> I'm currently accomplishing this by performing this statement, which isn't
> so fast when the number of rows gets large (10 million rows may be present
> in ii.rdgtemp).
>
> select * from ii.rdgtemp where rowid in ( select max(a.rowid) hirow from
> ii.rdgtemp a,
> ( select dev, max(quality) maxquality
> from II.RDGTEMP group by dev) b where
> a.dev = b.dev AND
> a.quality = b.maxquality
> group by a.dev ) )
>
> Anyone got a better way?
>
> Thanks for any and all advice!!!
>
> Phil Mattson
> Itron, Inc.
> phil.mattson_at_itron.com
> (509)-891-3748
Phil
1 8 2 9 3 4 19 9 20 4
SQL> Let me know if this is correct.
If you need to select other columns ie misc1,2 etc put a MAX/MIN around the column so the group by still works.
Jens. Received on Tue Jul 15 1997 - 00:00:00 CEST