PPC - SomeOne SpenDing - SomeOne EarNing

How to use rownum correctly in Oracle SQL ?

by Rocky Jagtiani

Oracle Certified Associate

I train on Oracle Certification from last 7 years

This is one such question , which freshers face a lot at on+off-campus interviews. The concept of rownum is quite simple. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

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

SELECT employee_id, first_name FROM Top_N_employees WHERE ROWNUM < 10;

You would notice only 9 rows (as the condition is < 10).

Contents : [ 20 mins read & execute ]

1> How to use rownum ?

2> Where not to use rownum ?

3> Face the most common interview question.

For beginners, who wish get the schema and records of my test table : Top_N_employees , do see here. 

For running all the queries, make sure of :

1> Installing Oracle-11g ( steps of installation and Oracle-11g download copy is here , please see Oracle SQL - PL/SQL > downloads section ). By the way any version from 9i and upwards would do. In case if you don't want to install Oracle , the use liveSQL from here.

2> Reading each and every step.

3> & finally Executing as you read on.  

How to use rownum ?

ROWNUM is a pseudo-column that is assigned an incremental, unique integer value for each row based on the order the rows were retrieved from a query. Therefore, the first row retrieved will have ROWNUM of 1; the second row will have ROWNUM of 2 and so on.

This feature can be used to limit the number of rows retrieved by a query.

The ROWNUM value is assigned to each row before any sorting of the result set takes place. Thus, the result set is returned in the order given by the ORDER BY clause, but the  ROWNUM values may not necessarily be in ascending order as shown : 

SELECT rownum, employee_id, first_name FROM  Top_N_employees WHERE ROWNUM < 5 order by salary;

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 5 smallest employee numbers. This is sometimes referred to as top-N reporting.

SELECT * FROM

   (SELECT * FROM top_n_employees ORDER BY employee_id)

   WHERE ROWNUM < 6;    

Where not to use rownum ?

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees

    WHERE ROWNUM > 1;

Firstly, ROWNUM is not a column, its a pseudo column which gets value only in the run time. The value of ROWNUM gets incremented by 1 only after fetching the row data.

When oracle fetches the first row it assigns the ROWNUM as 1 and then checks if the condition ROWNUM>1 is satisfied or not, as the condition is not satisfied the oracle engine skips the first row and now 2nd row would become the 1st row of our output and it gets assigned with ROWNUM as 1 (ROWNUM is not incremented as our first row is skipped) and again our condition ROWNUM>1 is false and this check goes for all the records of the output. The ROWNUM never gets incremented and no records will be fetched.

Thats why ROWNUM > N doesn’t work and ROWNUM = 1 or ROWNUM < N works fine.

Face the most common interview question.

Interviewer : I have a employee table in oracle with name,salary and other details. I am trying to get the second highest salary but not able to fetch.

This one working fine

with e_salary as (select distinct salary from top_n_employees)

select salary from e_salary

order by salary desc;

but when i am using the same query to fetch second highest row not getting any output

select salary from ( with e_salary as (select distinct salary from top_n_employees) 

select salary from e_salary order by salary desc) where rownum = 2;

  

but as I replace rownum=2 with rownum<2 font=""> it gives output of first two records. Please explain why rownum=2 is not working ?

Interviewee : Sure sir, I have learned Oracle SQL from Rocky Sir@Suven Consultants & Technology Pvt Ltd., I can answer this.

This will work:

with e_salary as (select distinct salary from top_n_employees) 

select salary from ( select salary, rownum as rn from (select salary from e_salary order by salary desc)) where rn = 2; 

Interviewer : correct. Good work !!

Interviewee : Sir, your query did not work because :

When assigning ROWNUM to a row, Oracle starts at 1 and only only increments the value when a row is selected; that is, when all conditions in the WHERE clause are met. Since our condition requires that ROWNUM is greater than 2, no rows are selected and ROWNUM is never incremented beyond 1.

Interviewer : By the way , can I have some information about training at Suven Consultants.

Interviewee : Yes sure. Its all their at training.suven.net

Interviewer : How early can you join us !!

Hope this explains "how & where to use rownum ?". Feel free to leave your comments below.