ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, … N , where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row. A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.
ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:
select * from t where ROWNUM > 1;
Below query will give you the wrong results,
select * from emp where ROWNUM <= 5 order by sal desc;
To understand the above, check the below query with this structure:
select ..., ROWNUM from t where <where clause> group by <columns> having <having clause> order by <columns>;
Think of it as being processed in this order:
- The FROM/WHERE clause goes first.
- ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
- SELECT is applied.
- GROUP BY is applied.
- HAVING is applied.
- ORDER BY is applied.
Below is the way ti use the rownum
select * from ( select * from emp order by sal desc ) where ROWNUM <= 5;