Re: Anyone have a better way??

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

I may be missing the point here but I get the results you are looking for using;

SQL> select * from jens;

       DEV QUALITY OWNER
---------- ---------- ----------

	 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

13 rows selected.

SQL> select dev, max(QUALITY) from jens group by DEV;

       DEV MAX(QUALITY)
---------- ------------

	 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 CDT