ROW_NUMBER() function in SQL

The ROW_NUMBER() function in SQL is used to assign a unique sequential integer to rows within a result set, based on a specified order.

SQL

ROW_NUMBER() OVER (ORDER BY column_name ASC|DESC)

Explanation:

ROW_NUMBER() generates the row numbers.
OVER specifies how the row numbers should be assigned.
ORDER BY determines the order in which the rows are numbered (ascending or descending).

Example: Create the Employees table.

SQL

CREATE TABLE Employees (
   EmployeeID INT,
    Name VARCHAR(20),
    Age int,
    Salary VARCHAR(10),
    Department VARCHAR(25),
    Gender VARCHAR(1)
);

SQL

CREATE TABLE Employees (
   EmployeeID INT,
    Name VARCHAR(20),
    Age int,
    Salary VARCHAR(10),
    Department VARCHAR(25),
    Gender VARCHAR(1)
);

Insert the data in the Employees table.

SQL

insert into Employees values
(1, "Ashish", 24, "25000", "Electrical", "M"), 
(2, "Kirshan", 32,"30000", "Electrical", "M"),
(3, "Anjali", 20,"25000", "Electronics", "F"),
(4, "Manjulika", 30, "15000", "Electrical", "F"),
(5, "Katrina", 37,"18000", "Computer", "F"),
(6, "Esha", 37,"20000", "Computer", "F"),
(7, "Ankita", 20,"9000", "Electronics", "M"), 
(8, "Meenakshi", 30, "15000", "Computer", "F"),
(10, "Alia", 37,"16000", "Electronics", "F")
;

By using the Create table and insert statement we reach to the following table in our database:

Table: Employees

EmployeeID	Name	Age	Salary	Department	Gender
1	Ashish	24	25000	Electrical	M
2	Kirshan	32	30000	Electrical	M
3	Anjali	20	25000	Electronics	F
4	Manjulika	30	15000	Electrical	F
5	Katrina	37	18000	Computer	F
6	Esha	37	20000	Computer	F
7	Ankita	20	9000	Electronics	M
8	Meenakshi	30	15000	Computer	F
10	Alia	37	16000	Electronics	F

Use the ROW_NUMBER() function to rank the rows according to the salary of the employees.

SQL

SELECT 
    EmployeeID, 
    Name, 
    Salary, 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM Employees;

This query assigns a row number to each employee, ordered by their salary in descending order (highest salary gets row number 1).

EmployeeID	Name	Salary	row_num
7	Ankita	9000	1
2	Kirshan	30000	2
1	Ashish	25000	3
3	Anjali	25000	4
6	Esha	20000	5
5	Katrina	18000	6
10	Alia	16000	7
4	Manjulika	15000	8
8	Meenakshi	15000	9

Explanation:

The rows are numbered based on the salary in descending order.
Ashish and Anjali both have a salary of 25,000, but they are given different row numbers because ROW_NUMBER() does not handle ties.

Example: Row Numbers Partitioned by Department We can also use ROW_NUMBER() to assign numbers within groups, for example, within each department.

SQL

SELECT 
    EmployeeID, 
    Name, 
    Salary, 
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM Employees;

EmployeeID	Department	Name	Salary	row_num
6	Computer	Esha	20000	1
5	Computer	Katrina	18000	2
8	Computer	Meenakshi	15000	3
2	Electrical	Kirshan	30000	1
1	Electrical	Ashish	25000	2
4	Electrical	Manjulika	15000	3
7	Electronics	Ankita	9000	1
3	Electronics	Anjali	25000	2
10	Electronics	Alia	16000	3

Explanation:

The PARTITION BY department clause ensures that row numbers are assigned within each department group.
The numbering restarts from 1 for each department, based on the salary order.

Previous Next