Re: where rownum =1 NOT producing expected TOP 1results with order by

On Feb 7, 3:27 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 7, 2:56 pm, "jobs" <j..._at_webdos.com> wrote:
>
> > This query does not give me the correct row. Am I missing something
> > here? I'm looking for the first row when sorting by that logdate..
> > thanks.
>
> > select logtype into g from CPM_ERRORLOG where cpmjobno=29 and
> > rownum=1 order by logdate desc;
>
> > Thanks.
>
> That is expected. ROWNUM is evaluated before the ORDER BY - I made
> the same logical mistake a couple times too.
>
> See:
> http://www.oracle.com/technology/oramag/oracle/07-jan/o17asktom.html
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.

Charles is right. And it is a good reason why you should avoid using ROWNUM.
Try
select logtype into g
 from CPM_ERRORLOG,
( select max(logdate) newestlog from CPM_ERRORLOG where cpmjobno=29 ) x
where cpmjobno=29 and x.newestlog=logdate ;

The FIRST row is seldom the answer to anything except a quick one-off.   HTH,
  ed Received on Wed Feb 07 2007 - 15:39:46 CST