Re: Using ROWNUM

On Oct 4, 4:56 am, Reds <redex1..._at_hotmail.com> wrote:
> Hi,
> I'm having a lot of problems using ROWNUM. Specifically, using ROWNUM in
> the where clause to access a particular row. It seem that if I use it
> as "ROWNUM = 1 " or "ROWNUM > 0", it works.
> But when I use it as "ROWNUM = 2" or "ROWNUM > 1", no rows are selected.
>
> Here are some query examples:
>
> THIS WORKS:
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE ROWNUM = 1;
>
> STU_ID LNAME FNAME M S MAJOR HOME
> ---------- ---------- ---------- - - ---------- ----
> 10001 Smith Ron M M Math Tx
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE ROWNUM > 0;
>
> STU_ID LNAME FNAME M S MAJOR HOME
> ---------- ---------- ---------- - - ---------- ----
> 10001 Smith Ron M M Math Tx
> 10002 Jones Peter A M English Tx
> 10003 Peters Anne A F English Me
> 10004 Johnson John J M CompSci Ca
> 10005 Penders Alton P F Math Ga
> 10006 Allen Diane J F Geography Minn
> 10007 Gill Jennifer F F CompSci Tx
> 10008 Johns Roberta A F CompSci Ala
> 10009 Wier Paul A M Math Ala
> 10010 Evans Richard A M English Tx
>
> 10 rows selected.
>
> BUT THIS DOESN'T WORK:
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE rownum > 1;
>
> no rows selected
>
> SQL> SELECT *
> 2 FROM Student
> 3 WHERE ROWNUM = 2;
>
> no rows selected
>
> ******************************************************
> Here are some PL/SQL exampleS
>
> THIS WORK:
>
> SQL> set serveroutput on
> SQL> DECLARE
> 2
> 3 v_ID Student.Stu_ID%TYPE;
> 4 v_Rowcount NUMBER := 1;
> 5
> 6 BEGIN
> 7
> 8 LOOP
> 9 SELECT Stu_ID
> 10 INTO v_ID
> 11 FROM Student
> 12 WHERE ROWNUM = 1;
> 13 DBMS_OUTPUT.PUT_LINE('Row Number : '||v_Rowcount||', Student ID:
> '|| v_ID)
> ;
> 14 v_Rowcount := v_Rowcount + 1;
> 15
> 16 EXIT ;
> 17 END LOOP;
> 18 END;
> 19 /
> Row Number : 1, Student ID: 10001
>
> PL/SQL procedure successfully completed.
>
> BUT THIS DOESN'T WORK:
>
> SQL> DECLARE
> 2
> 3 v_ID Student.Stu_ID%TYPE;
> 4 v_Rowcount NUMBER := 1;
> 5
> 6 BEGIN
> 7
> 8 LOOP
> 9 SELECT Stu_ID
> 10 INTO v_ID
> 11 FROM Student
> 12 WHERE ROWNUM = 2;
> 13 DBMS_OUTPUT.PUT_LINE('Row Number : '||v_Rowcount||', Student ID:
> '|| v_ID)
> ;
> 14 v_Rowcount := v_Rowcount + 1;
> 15
> 16 EXIT ;
> 17 END LOOP;
> 18 END;
> 19 /
> DECLARE
> *
> ERROR at line 1:
> ORA-01403: no data found
> ORA-06512: at line 9
>
> BTW, does anyone has a suggestion as to how to use the SELECT INTO to
> sequentially access each row? I thought the ROWNUM would be a good
> parameter to use, but it's not cooperating.
>
> Thanks

In addition to David Fitzjarrell detailed reply, you may want to take a look at the follow:
http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html

The above is a reprint of an article from Oracle's magazine where Tom Kyte describes how ROWNUM works - the article answered a couple questions for me the first time that I read it. For example: SELECT
  *
FROM
  STUDENT
WHERE
  ROWNUM = 1
ORDER BY
  LNAME; When the above is evaluated, the ROWNUM restriction is applied before the ORDER BY - that detail caught me a number of times, but made since once I read the article. The article also describes why ROWNUM > 1 cannot be specified.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Oct 04 2007 - 09:17:54 CDT