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

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

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

the result of your query is ... 3

your query makes no sense. You are selecting rownum, which you give as a criteria in your query. So you already know the result...

Grtz

Answer 4

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.