Re: Anyone have a better way??

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/07/12
Message-ID: <33c7ca4b.13619699_at_www.sigov.si>#1/1

On 11 Jul 97 21:44:06 GMT, "Phil Mattson" <phil.mattson_at_itron.com> wrote:

>Hello all,
> .....[SNIP].......
>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?

Try this one. Comparing both execution plans it looks much more promising.

SELECT a.* FROM
  ii.rdgtemp a,
  (SELECT dev, MAX(TO_CHAR(quality,'09999999')||rowid) maxquality

     FROM ii.rdgtemp GROUP BY dev) b
WHERE a.rowid = chartorowid(substr(b.maxquality,10,18))

>Thanks for any and all advice!!!
>
>Phil Mattson

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Sat Jul 12 1997 - 00:00:00 CEST