Use ROWNUM in PL/SQL

1. ROWNUM is a pseudo column. It acts like a column but it doesn’t exist in a real table. It only exists in the result set returned from a query. 
2.  ROWNUM numbers the records in a result set. The first record is assigned 1 to ROWNUM and it always starts from 1. Each subsequent record is assigned incrementally. 
3.  ROWNUM assignment is done after the select statement returns the result set and before the result set is ordered.

Here are some examples that demonstrate how to use ROWNUM correctly.

Example 1: 

select * from pa_tasks where task_name = 'ICT Costs'

This is a normal query which returns the row without order.

Example 2: 

select rownum, TASK_ID, PROJECT_ID, TASK_NUMBER, CREATION_DATE from pa_tasks where task_name = 'ICT Costs'

This is a normal query but with rownum displayed as the part of the result set.

Example 3:

select * from pa_tasks where task_name = 'ICT Costs'   order by task_id;

This is a normal query with ordering by TASK_ID.

Example 4:

select * from pa_tasks where task_name = 'ICT Costs' and rownum <= 3  order by task_id;

This query tries to select the first 3 rows of the result set ordered by TASK_ID.   From the result shown above it doesn’t do what is expected to do.  Because ordering happens after the ROWNUM is assigned.

The correct way is using subquery as shown in example 5.

Example 5:

select * from (select * from pa_tasks where task_name='ICT Costs' order by task_id) where rownum <= 3;

Example 6:

select * from pa_tasks where task_name = 'ICT Costs' and rownum > 3

This query tries to select rows starts from 4.   But unfortunately no records will be selected from the query because rownum > 3 contradicts with rownum must start with 1.   So when using rownum in where clause always use it with “<” or “<=”.

In order to achieve what is expected the correct way to do is use the subquery where rownum is assigned and displayed as the field value.

Example 7:

select * from (select rownum r, TASK_ID, PROJECT_ID, TASK_NUMBER, CREATION_DATE from pa_tasks where task_name = 'ICT Costs') where r > 3