Understanding the RANK() Function in SQL with Practical Examples

This article provides a better description of the SQL RANK() and with a way of executing such as well as its utility. We shall discuss how, when an ordering is specified, RANK () assigns ranks by row to a partition of a result set. It includes some example with sample tables data. The RANK() function assigns a rank to each row within a partition and it is based on the order of the specified column (or columns). It is also here that equal rankings are given to two or more rows sharing the same value of the column or columns used in the ranking. Due to missing the next level in the ranking, it also has elements where there are gaps in the ranking sequence.

Overview of RANK()

Each row inside a partition of a result set is given a rank by the SQL window function RANK(). The OVER() clause's supplied ORDER BY clause serves as the basis for the rank. The same rank is given to rows that have similar values for the ordering criteria. The number of rows with the same rank is added to the next rank. This implies that the ranking sequence may have pauses caused by RANK().

The following is the RANK() function's fundamental syntax:

RANK() OVER (  
    [PARTITION BY column1, column2, ...]  
    ORDER BY column3 [ASC | DESC], column4 [ASC | DESC], ...  
)  

PARTITION BY (Optional): A clustering of the result set. The ranks of each of the partitions are considered independently. When the PARTITION BY is not provided, the whole result set is considered as one partition.
ORDER BY: Contains the name of the column(s) which specify the ranking order in each partition. The default order is ASC (ascending) and reverse order can be in descending order.
OVER(): Defines the window (set of rows) to which the function applies.

Example:

Let's consider a table named Employees with the following structure and data:

--create objects Employees   
CREATE TABLE Employees (         
EmployeeID INT PRIMARY KEY    
,Department VARCHAR(50)    
,Salary DECIMAL(10, 2)     
);    
  
    
-- insert  in Employees   
INSERT INTO Employees (EmployeeID, Department, Salary)     
VALUES    
 (1, 'Sales', 60000.00),    
 (2, 'Marketing', 55000.00),     
(3, 'Sales', 70000.00),     
(4, 'IT', 80000.00),     
(5, 'Marketing', 55000.00),     
(6, 'IT', 90000.00),     
(7, 'Sales', 70000.00),     
(8, 'HR', 65000.00),     
(9, 'HR', 75000.00),     
(10, 'IT', 85000.00);    

Example 1: Ranking Employees by Salary

the objective was to rank the employees according to the salary paid.

We can tabulate the employees in the terms of salary throughout the company.

--get data Employees
SELECT EmployeeID, Department, Salary ,RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employees;

--OutPut
EmployeeID Department Salary SalaryRank
----------- ------------------------------ ------------------------------ --------------------
6 IT 90000.00 1
10 IT 85000.00 2
4 IT 80000.00 3
9 HR 75000.00 4
3 Sales 70000.00 5
7 Sales 70000.00 5
8 HR 65000.00 7
1 Sales 60000.00 8
2 Marketing 55000.00 9
5 Marketing 55000.00 9

This table demonstrates the hierarchy of every employee based on the salary.

Workers of identical salary receive identical rank. As salaries increase or decrease so does the rank. Employees 3 and 7 are at the same salary and will hence have same rank 5. The sixth rank, rank 6 is avoided as no one makes that much.