Re: ROWNUM question??

From: <mark.powell_at_eds.com>
Date: Wed, 20 Jan 1999 14:15:53 GMT
Message-ID: <784oeg$7ot$1@nnrp1.dejanews.com>

Oscar and Aj, The problem is because Oracle assigns the rownum to the row before the rows are passed to sort.

  Example:
TRAIN> select ename from emp where rownum < 5;

ENAME

---

SMITH
ALLEN
WARD
JONES   Find the first row equal to ( rownum = 1 ) TRAIN> select ename
  2 from emp
  3 where rownum = 1;

ENAME

---

SMITH   By adding a sort all we do is sort this one, first retrieved row TRAIN> select ename
  2 from emp
  3 where rownum = 1
  4 order by ename
  5 /

ENAME

---

SMITH   We can verify this by increasing the number of rows we retrive to two TRAIN> select ename from emp
  2 where rownum < 3
  3 order by ename
  4 /

ENAME

---

ALLEN
SMITH Notice that we got the first two rows in the table, not the two lowest value rows, and sorted them. To get the first row by sort order value you can use a pl/sql cursor:

TRAIN> set serveroutput on
TRAIN> declare
  2 v_ename emp.ename%type;
  3 cursor c_first_row is
  4 select ename
  5 from emp
  6 order by ename;
  7 begin
  8 open c_first_row;
  9 fetch c_first_row into v_ename;
 10 close c_first_row;
 11 dbms_output.put_line(v_ename);
 12 end;
 13 /
ADAMS PL/SQL procedure successfully completed.

I hope this helps everyone.

Mark D. Powell

In article <7837qv$fc7$1_at_ash.prod.itd.earthlink.net>,   "Aj" <ajkumar_at_earthlink.net> wrote:
> Oracle rownum wouldnever be 1. They are long crazy numbers... Try this
> rownum < 2
>
> Aj
> Oscar Paredes wrote in message <36A4E33D.74474199_at_fib.upc.es>...
> >
> >Hi all!
> >
> >I want to select the first row of a SELECT statement
> >that have a ORDER BY, like:
> >
> > select e.name
> > from employee e
> > where rownum=1
> > order by e.name
> >
> >But,... unfortunatelly... this don't work!! :-(
> >
> >Someone know how to do this????
> >
> >
> >Thanks,
> >Oscar

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Jan 20 1999 - 08:15:53 CST