select rownum from salary where rownum=3;

Question

How to retrieve third row from any table using "rownum" key word ( i am using oracle-10g)

Answer 1

Oracle assigns values to ROWNUM sequentially as rows are produced by the query - thus, the first row fetched gets ROWNUM=1, the second row fetched gets ROWNUM=2, the third row fetched gets ROWNUM=3, etc. Notice - for a row to be assigned ROWNUM=3 two preceding rows MUST be fetched. And this is why your query returns no rows. You're asking the database for the third row fetched - but rows 1 and 2 have never been fetched.

To demonstrate, try running the following queries:

SELECT S.* FROM SALARY S;          -- Should return all rows
SELECT ROWNUM, S.* FROM SALARY S;  -- Should return all rows with ROWNUM prepended
SELECT ROWNUM, S.* FROM SALARY WHERE ROWNUM=3;  -- Should return no rows

To work around your problem, try the following:

SELECT ROW_NUMBER FROM
  (SELECT ROWNUM AS ROW_NUMBER, S.* FROM SALARY S)
  WHERE ROW_NUMBER = 3;

Share and enjoy.

Answer 2

You would need to do something like this

select rnum,sal
  from  
( select sal, rownum rnum

    from salary 
   order by sal desc ) 
 where rnum = 3;

rownum is not assigned until after the predicate phase so rownum = 3 will always be false. Use a CTE or derived table then you can access the rownum from outside it.

Answer 3

SELECT ROW_NUMBER FROM   (SELECT ROWNUM AS ROW_NUMBER, S.* FROM SALARY S)   WHERE ROW_NUMBER = 3; 

This is incorrect. This will always return '3', because you are selecting ROW_NUMBER.

It should instead be "select *", as mentioned below:

select * from (select rownum as row_number, s.* from salary s) where row_number = 3;

Answer 4

Is rownum an actual column in your salary table? If not, depending on your DB type, rownum is likely not supported.