Retrieving from DB using while rownum works only when rownum = 1

Question

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.

Answer 1

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>