retrieveing from db using while Rownum works only when rownum = 1

I'm trying to retrieve data from database based on rownum value,

select * from MY_TABLE WHERE ROWNUM = 4

it only gives the result when the rownum = 1, otherwise it's empty. Even though I'm sure my table has more rows!

              name      age 
--------     --------  -------  
1             Joe       10     
2             Leo       3    
3             Adam      9

For example the table above I can only get joe's info, by saying while rownum = 1, the others just give no result

2 answers

  • answered 2018-04-17 05:33 Nishant Gupta

    ROWNUM returns a number indicating the order in which Oracle select the row from the table. The first row has ROWNUM 1, the second 2 etc.

    With a query like

    SELECT * FROM table
    WHERE ROWNUM = 2
    

    the first row fetched has ROWNUM 1, and makes the where condition false. The next row, the second, is now the first row and has ROWNUM 1, and makes the where condition false, etc.

  • answered 2018-04-17 05:33 Littlefoot

    Suppose that this is your table:

    SQL> select * from my_table;
    
    NAME                        AGE
    -------------------- ----------
    Joe                          10
    Leo                           3
    Adam                          9
    Yura                          1
    

    ROWNUM can't be used with the "=" sign (Nishant Gupta told you why):

    SQL> select * from my_table where rownum = 3;
    
    no rows selected
    
    SQL>
    

    But, you can use "<=":

    SQL> select * from my_table where rownum <= 3;
    
    NAME                        AGE
    -------------------- ----------
    Joe                          10
    Leo                           3
    Adam                          9
    
    SQL>
    

    Or, if you want to use "=" anyway (as you need the 3rd value only), you'll have to use an inline view which selects ROWNUM (and alias it, as "RN" - for example) along with ORDER BY clause, such as

    SQL> select name, age
      2  from (select rownum rn, name, age
      3        from my_table
      4        order by age
      5       )
      6  where rn = 3;
    
    NAME                        AGE
    -------------------- ----------
    Adam                          9
    
    SQL>
    

    Or, using the ROW_NUMBER analytic function:

    SQL> select name, age
      2  from (select name, age, row_number() over (order by age) rn
      3        from my_table
      4       )
      5  where rn = 3;
    
    NAME                        AGE
    -------------------- ----------
    Adam                          9
    
    SQL>