RANK function in SQL

The RANK() function in SQL is used to assign a rank to rows within a result set based on a specified order. The ranks are assigned starting from 1 for each distinct value. If two or more rows have the same rank, they receive the same rank, and the next rank is skipped.

SQL

RANK() OVER (
    [PARTITION BY column_name(s)] 
    ORDER BY column_name [ASC|DESC]
)

Explanation:

PARTITION BY: (Optional) Divides the result set into partitions and applies the RANK() function to each partition.
ORDER BY: Specifies the order of the rows based on which the rank will be assigned.

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 Rank() function to rank the rows according to the salary of the employees.

SQL

SELECT 
    EmployeeID, 
    Name, 
    Salary, 
    RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

The above query ranks employees based on their salary in descending order (highest salary gets rank 1).

EmployeeID	Name	LENGTH(Name)
1	Ashish	6
2	Kirshan	7
3	Anjali	6
4	Manjulika	9
5	Katrina	7
6	Esha	4
7	Ankita	6
8	Meenakshi	9
10	Alia	4

Notice that Ashish and Anjali have the same salary, so they are both ranked 3, and the next rank is 5, skipping rank 4. Similarly, Manjulika and Meenakshi have the same rank.

Note: The RANK() is different from ROW_NUMBER(). The ROW_NUMBER() gives a unique rank even to tied rows.

Previous Next