ROW_NUMBER clause in Oracle SQL – PLSQL

The ROW_NUMBER clause in Oracle SQL / PLSQL is basically a windowing clause and is used to assign a unique row number to fetched records based on an ordered list. ROW_NUMBER clause starts numbering from 1.

ROW_NUMBER clause can be used with and without PARTITION BY clause.

Syntax for using the ROW_NUMBER clause without PARTITION BY clause in Oracle SQL / PLSQL is;
SELECT column(s)
ROW_NUMBER () OVER (ORDER BY column)
FROM table_name;

Syntax for using the ROW_NUMBER clause with PARTITION BY clause in Oracle SQL / PLSQL is;
SELECT column(s)
ROW_NUMBER () OVER (PARTITION BY column ORDER BY column)
FROM table_name;

Example 1:
Using ROW_NUMBER () clause without PARTITION BY clause

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

Now, suppose we want to see all the records from the employee table and also want to assign a unique row number to each row, then we can achieve the same as:

SELECT ROW_NUMBER () OVER (ORDER BY e.commission) SNo
       ,e.*
FROM employee e;

We will get the following result:

SNo Employee_ID Employee_Name Department Salary Commission
1 104 Emp D Support 30000 5
2 108 Emp H Sales 12000 10
3 105 Emp E Sales 32000 10
4 106 Emp F Sales 20000 10
5 107 Emp G Sales 12000 10
6 101 Emp A Sales 10000 10
7 102 Emp B IT 20000 20
8 103 Emp C IT 28000 20

Here we can see that we have retrieved all the rows from employee table and also assigned a unique row number as ‘SNo’, for convenience.


Example 2:

Using ROW_NUMBER () clause with PARTITION BY clause

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales 10
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 40000 Sales 10
107 Emp G 12000 Sales 10
108 Emp H 12000 Sales 10

Now, suppose we want to see all the records from the employee table grouped by department and also want to assign a unique row number to each row, then we can achieve the same as:

SELECT ROW_NUMBER () OVER (PARTITION BY e.department ORDER BY e.commission) SNo
       ,e.*
FROM employee e;

We will get the following result:

SNo Employee_ID Employee_Name Department Salary Commission
1 102 Emp B IT 20000 20
2 103 Emp C IT 28000 20
1 101 Emp A Sales 10000 10
2 108 Emp H Sales 12000 10
3 105 Emp E Sales 32000 10
4 106 Emp F Sales 20000 10
5 107 Emp G Sales 12000 10
1 104 Emp D Support 30000 5

Here we can see that we have retrieved all the rows from employee table and also assigned a unique row number to each row department wise i.e. as soon as the department changes the row number starts from 1 till other department is encountered.


Tagged , , , , , , , , , , , . Bookmark the permalink.