RANK() Function in SQL Server

SQL RANK() function is a window function used in SQL Server that calculates the rank of each row of the result set.

RANK Function in SQL Server

The RANK function in the SQL server is used to assign a rank to each row based on its value.

The same rank is assigned to the rows which have the same values. The ranks may not be consecutive in the RANK() function as it adds the number of repeated rows to the repeated rank to calculate the rank of the next row. 

Syntax

The Syntax to use the RANK function in SQL Server is: 

RANK() OVER (
   [PARTITION BY expression, ]
   ORDER BY expression (ASC | DESC) );

Note:

Some other Rank functions used in SQL Server are:

• ROW_NUMBER()
• DENSE_RANK()
• NTILE()

SQL RANK Function Example

Let’s look at some examples of the RANK function in SQL server to understand how it’s working.

Let’s create a demo table on which we will perform the RANK statement. Write the following queries to create a table “geek_demo”.

CREATE TABLE geek_demo (Name VARCHAR(10) );
INSERT INTO geek_demo (Name)
VALUES ('A'), ('B'), ('B'), ('C'), ('C'), ('D'), ('E');
SELECT * FROM sales.geek_demo; 

Output

Name
A
B
B
C
C
D
E

In this example, we will use RANK() to assign ranks to the rows in the result set of the geek_demo table.

Query:

SELECT Name, 
RANK () OVER (
ORDER BY Name
) AS Rank_no 
FROM geek_demo;

Output –

Name	Rank_no
A	1
B	2
B	2
C	4
C	4
D	6
E	7

Important Points About SQL RANK Function

• The SQL RANK function is a window function used in SQL Server to calculate a rank for each row.
• It assigns a unique rank to each row within the partition, with gaps in rank values if there are ties.
• The RANK function allows you to rank rows based on a specified column or set of columns, providing a clear order of precedence within the result set.
• When we use the SQL RANK function with ORDER BY clause, the result set is returned with sorted rows in each partition where the RANK function is applied.
• The same rank is assigned to the rows which have the same values.