Rank Functions in SQL server

Introduction

In this article, we will learn about Rank Functions in SQL Server. Find more about Functions in SQL Server here- Functions in SQL Server

ROW_NUMBER() OVER ([PARTITION BY CLAUSE] <ORDER BY CLAUSE>) - Returns the sequential number of a row within the partition of a result set at 1 for the first row of each partition.
RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLAUSE>) - Returns rank for rows within the partition of the result set.
DENSE_RANK() OVER ([PARTITION BY CLAUSE] <ORDER BY CLAUSE>) - Returns rank for rows within the partition of the result set. Without any gaps in the ranking.
NTILE(INTEGER_EXPRESSION) OVER ([PARTITION BY CLAUSE] <ORDER BY CLAUSE>) - Distributes the rows in an ordered partition into a specified number of groups.

Examples

Let's start with creating a table in SQL Server. We will create an Employee table with a few columns. You can copy and execute the following SQL statement in SSMS.

--create Employee table  
create table Employee  
(  
   EmpId int identity(1,1) primary key,  
   FirstName varchar(100),  
   LastName varchar(100),  
   JoinDate datetime ,  
   Salary int ,  
   Department varchar(20)  
)

The above SQL statement creates a new table in the database called "Employee" with the specified columns and their data types. The table will have a primary key column called "EmpId" that is set as an identity column, meaning that it will automatically be assigned a new value for each new row inserted into the table.

Once the table is created, you can use various SQL statements to insert, update, delete, and query data from the table. For example, you can use the INSERT statement to add new rows to the table by using the below SQL query.

--Insert data to Employee table  
  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Rakesh','Kalluri','2012-07-01 10:00:00.000',20000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Shabari','Vempati','2011-05-01 10:00:00.000',25000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Venkatesh','Bodupaly','2013-04-01 10:00:00.000',15000,'Bpo')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Surjan','Peddineni','2011-07-01 10:00:00.000',25000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Nani','Ch','2010-07-01 10:00:00.000',50000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raju','Chinna','2012-07-01 10:00:00.000',25000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Kiran','Kumar','2011-07-01 10:00:00.000',20000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Raki','Kumar','2012-07-01 10:00:00.000',17000,'Bpo')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Sri','Vidya','2011-07-01 10:00:00.000',30000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Fehad','MD','2013-07-01 10:00:00.000',20000,'Bpo')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Anusha','Kumari','2011-07-01 10:00:00.000',35000,'Software')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department) values('Venky','Naidu','2013-07-01 10:00:00.000',20000,'Bpo')  
insert into Employee(FirstName,LastName,JoinDate,Salary,Department)values('Radha','Kumari','2012-07-01 10:00:00.000',10000,'Bpo')

Using the above query, insert the data into the Employee table.

--selecting data from Employee table  
select * from Employee

The Employee table contains columns, and the data is retrieved using a SELECT statement. All rows of data and columns of data from the table will be returned by the database engine when you execute this statement, as shown in the below snapshot.

--Row_Number() with out using partition clause  
select * ,row_number() over (order by Salary desc) as Row_Num from Employee

The row number is determined using the ROW_NUMBER() function, which assigns a unique number to each row based on the order specified in the OVER() clause. In this case, the rows are ordered by the "Salary" column in descending order.

--Row_Number() with using partition clause  
select * ,row_number() over (partition by Department order by Salary desc) as Row_Num from Employee

In this case, the partition is defined by the "Department" column, so each department will have its own set of row numbers that are assigned to the rows within that department. The rows within each department are ordered by the "Salary" column in descending order.

--rank() with out using partition clause  
select * ,rank() over (order by Salary desc) as [Rank] from Employee

The above SELECT statement retrieves all columns of data from the "Employee" table, along with an additional calculated column called "Rank" that assigns a rank to each row based on the "Salary" column. The rank is calculated using the RANK() function, which assigns a rank to each row based on the order specified in the OVER() clause. In this case, the rows are ordered by the "Salary" column in descending order.

The RANK() function assigns a rank to each row based on its value compared to the values of the other rows in the result set. If two or more rows have the same value, they are assigned the same rank, and the ranks of the rows following them are incremented by the number of tied rows. So while executing this SELECT statement, it retrieves all rows of data from the "Employee" table, along with a calculated "Rank" column that assigns a rank to each row based on the "Salary" column as shown below snapshot.

--rank() with using partition clause  
select * ,rank() over (partition by Department order by Salary desc) as [Rank] from Employee

The above SELECT statement retrieves all columns of data from the "Employee" table, along with an additional calculated column called "Rank" that assigns a rank to each row within each department based on the "Salary" column. The rows are ordered by the "Salary" column in descending order within each department. The rank is calculated using the RANK() function.

--dense_rank() with out using partition clause  
select * ,dense_rank() over (order by Salary desc) as [Dense_rank] from Employee

The above SELECT statement retrieves all columns of data from the "Employee" table, along with an additional calculated column called "Dense_rank" that assigns a rank to each row based on the "Salary" column. The rows are ordered by the "Salary" column in descending order. The rank is calculated using the DENSE_RANK() function, which assigns ranks to the rows without any gaps between the ranks.

--dense_rank() with using partition clause  
select * ,dense_rank() over (partition by Department order by Salary desc) as [Dense_rank] from Employee

The above SELECT statement retrieves all columns of data from the "Employee" table, along with an additional calculated column called "Dense_rank" that assigns a rank to each row within each department based on the "Salary" column. The rows are ordered by the "Salary" column in descending order within each department. The rank is calculated using the DENSE_RANK() function, which assigns ranks to the rows without any gaps between the ranks within each department.

--ntile(input_exp) with out using partition clause  
select * ,ntile(3) over (order by Salary desc) as [ntile] from Employee

The above SELECT statement retrieves all columns of data from the "Employee" table, along with an additional calculated column called "ntile" that assigns a group number to each row based on the "Salary" column. The rows are ordered by the "Salary" column in descending order. The group numbers are calculated using the NTILE() function, which divides the rows into the specified number of groups (in this case, 3 groups).

--ntile(input_exp) with using partition clause  
select * ,ntile(3) over (partition by De6partment order by Salary desc) as [ntile] from Employee

The above SELECT statement retrieves all columns of data from the "Employee" table, along with an additional calculated column called "ntile" that assigns a group number to each row within each department based on the "Salary" column. The rows are ordered by the "Salary" column in descending order within each department.

The group numbers are calculated using the NTILE() function, which divides the rows within each department into the specified number of groups (in this case, 3 groups).

Conclusion

In this article, we learned about Rank Functions in SQL Server.