Rownum and order by in oracle


ROWNUM is a pseudocolumns 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 not yield any results
  5. ROWNUM and ROW_NUMBER() is not same.

Please comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: