Rownum and order by in oracle

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

1. Do not write ROWNUM = 0 ( There is no record having ROWNUM = 0)
2. Do not write ROWNUM <= 1 instead write ROWNUM = 1
3. In general Oracle assigns ROWNUM while retrieving the records, it is not based on Primary key column
4. Do not write ROWNUM > 1, which will no yield any results
5. ROWNUM and ROW_NUMBER() is not same.

Please comment.