ROWNUM
ROWNUM is a psuedocolumn to return the row number of the row in a table. The order will be based on how oracle selects a row from table.
Examples with rownum
Note that rownum will be decided by Oracle itself ( Oracle will assign a number based on how it retrieves from storage/memory)
Order by
Order by is a sql clause which used to order a selected column
Example :-
Order by and Rownum together
Why we should use rownum and Order by clause together ? Many experienced developers ( Mainly java developers ) think they can write rownum and order by in same where clause to get the top N query.
For example – To find lowest salary employee developers write like below
select * from emp where rownum = 1 order by salary asc ; — wrong query
see the result
Result is wrong. It should be salary 7500 ( Empno – 14 )
Why ? Oracle applies the ROWNUM first and then applies the order by clause.
See the correct query below
So always apply the order by and in next level apply the rownum.
To find a lowest salary employee :-
select * from emp where rownum = 1 order by salary asc ; — wrong query
select * from (select * from emp order by salary asc) where rownum = 1 — correct query
Few more tips about rownum
- Do not write ROWNUM = 0 ( There is no record having ROWNUM = 0)
- Do not write ROWNUM <= 1 instead write ROWNUM = 1
- In general Oracle assigns ROWNUM while retrieving the records, it is not based on Primary key column
- Do not write ROWNUM > 1, which will no yield any results
- ROWNUM and ROW_NUMBER() is not same.
Please comment.