Select n'th row using ROWNUM Oracle

Question

I wonder why 1 doesn't work, what's the logic behind adding ROWNUM rn and using rn in the outer query that makes it work in 2?

Also I thought using ROWNUM = somenumber (except 1) won't work in oracle, what's making it work here in 2?

1.

SELECT SALARY
FROM (
    SELECT SALARY
    FROM EMPLOYEE
    WHERE ROWNUM <= N
    )
WHERE ROWNUM >= N;
RETURN result;

2.

SELECT SALARY
FROM (
    SELECT SALARY, ROWNUM rn
    FROM EMPLOYEE
    WHERE ROWNUM <= N
    )
WHERE rn = N;
RETURN result;

Why not just `SELECT SALARY FROM EMPLOYEE WHERE ROWNUM = N` ? — Tim Biegeleisen, Oct 23 '18 at 05:51

Florin Ghita · Accepted Answer · 2018-10-23T06:12:21.063

In the second query, you are not using rownum but a fixed value: rn. Its values are computed in the subquery.

In the first query, the second rownum is not the same rownum as the rownum in the subselect. The rownum in the subselect works for the rowset in the subselect. And the rownum in the outer query works for the outer query. So, your first query is seen by oracle like:

SELECT SALARY
FROM something
WHERE ROWNUM >= N;
RETURN result;

This won't give records because first row is rownum = 1 and its not >= N. And the second row fetched its now the first and it's not >= N. And so on.

See here another question with the same issue.

If you want, the rownum is the last thing computed. It is a assigned when a row is fetched. So, always the first row has rownum = 1. :)

Select n'th row using ROWNUM Oracle

1 Answers1