Anyone have a better way??
Date: 1997/07/11
Message-ID: <01bc8e45$af462a30$b30ca8c0_at_mattson>#1/1
[Quoted] Hello all,
[Quoted] 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 .............
[Quoted] [Quoted] I'm currently accomplishing this by performing this statement, which isn't [Quoted] so fast when the number of rows gets large (10 million rows may be present [Quoted] in ii.rdgtemp).
[Quoted] 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?
[Quoted] Thanks for any and all advice!!!
Phil Mattson
Itron, Inc.
phil.mattson_at_itron.com
(509)-891-3748
Received on Fri Jul 11 1997 - 00:00:00 CEST