Re: Anyone have a better way??

From: John L. Viescas <JohnLV_at_nomail.Please>
Date: 1997/07/14
Message-ID: <01bc906a$4a8207a0$942e2299_at_jv-tecra>#1/1


That's a really creative solution that involves specific knowledge of Oracle built-in functions and how Oracle handles its Rowid. A perfect example of why Access supports Passthrough queries!

-- 
John 

Jurij Modic <jurij.modic_at_mf.sigov.mail.si> wrote in article
<33c7ca4b.13619699_at_www.sigov.si>...

> 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 Mon Jul 14 1997 - 00:00:00 CEST

Original text of this message