Anyone have a better way??
Date: 1997/07/11
Message-ID: <01bc8e45$af462a30$b30ca8c0_at_mattson>#1/1
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
An example of the data would be:
dev quality owner misc1 misc2 ...
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 ...
A single table (ii.rdgtemp) has a dozen columns. The table is "ordered by
dev ASC, quality DESC".
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 .............
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
Received on Fri Jul 11 1997 - 00:00:00 CEST