Rownum and Rowid


select rownum,e.* from HR.employees e;

The order is proper but when we add a order by clause the rownum becomes messy

select rownum,e.* from HR.employees e order by FIRST_NAME;
  • This is because the rownum is execute first and after which the order by clause is execurted which messes up the rownum order.
  • To fix this we should create a query without rownum first and then encapsuldate the result a inline view, example below
select rownum, result.* from 
(select e.* from HR.employees e order by FIRST_NAME) result;

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;


For each row in the database, the ROWID pseudocolumn returns the address of the row. 

  • They are the fastest way to access a single row.
  • They can show you how the rows in a table are stored.
  • They are unique identifiers for rows in a table.

Example :

SELECT ROWID, last_name  
   FROM employees
   WHERE department_id = 20;

Leave a Comment