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

A fine example of why I have always opposed the use of ROWNUM. Other than taging a number on a report it really is useless.

Remember, in a relational DB there is NO ORDER to the data. It is a SET. If from some reason you do want the second row just use
> 9 SELECT Stu_ID
> 10 INTO v_ID
> 11 FROM Student
> 12 WHERE ROWNUM = 1;

(If you can tell me the difference in the resulting row fetched from the one you want, then I will tell you what your WHERE clause shuld really look like.)

Basically stop thinking of tables as if they were flat files!

HTH (along with the other fine posts from Laurenz, Shakespeare, David and Charles),

   Ed Received on Thu Oct 04 2007 - 13:09:06 CDT