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.