Anyone have a better way??

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 Received on Fri Jul 11 1997 - 00:00:00 CDT