SQL Server RANK Function

Summary: in this tutorial, you will learn how to use SQL Server RANK() function to calculate a rank for each row within a partition of a result set.

Introduction to SQL Server RANK() function #

The RANK() function is a window function that assigns a rank to each row within a partition of a result set.

The rows within a partition that have the same values will receive the same rank. The rank of the first row within a partition is one. The RANK() function adds the number of tied rows to the tied rank to calculate the rank of the next row, therefore, the ranks may not be consecutive.

The following shows the syntax of the RANK() function:

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, the PARTITION BY clause divides the rows of the result set partitions to which the function is applied.
• Second, the ORDER BY clause specifies the logical sort order of the rows in each a partition to which the function is applied.

The RANK() function is useful for top-N and bottom-N reports.

SQL Server RANK() illustration #

First, create a new table named sales.rank_demo that has one column:

CREATE TABLE sales.rank_demo (
	v VARCHAR(10)
);
Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the sales.rank_demo table:

INSERT INTO sales.rank_demo(v)
VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');
Code language: SQL (Structured Query Language) (sql)

Third, query data from the sales.rank_demo table:

SELECT v FROM sales.rank_demo;Code language: SQL (Structured Query Language) (sql)

Fourth, use the ROW_NUMBER() to assign ranks to the rows in the result set of sales.rank_demo table:

SELECT
	v,
	RANK () OVER ( 
		ORDER BY v 
	) rank_no 
FROM
	sales.rank_demo;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

As shown clearly from the output, the second and third rows receive the same rank because they have the same value B. The fourth and fifth rows get the rank 4 because the RANK() function skips the rank 3 and both of them also have the same values.

SQL Server RANK() function examples #

We’ll use the production.products table to demonstrate the RANK() function:

Using SQL Server RANK() function over a result set example #

The following example uses the RANK() function to assign ranks to the products by their list prices:

SELECT
	product_id,
	product_name,
	list_price,
	RANK () OVER ( 
		ORDER BY list_price DESC
	) price_rank 
FROM
	production.products;
Code language: SQL (Structured Query Language) (sql)

Here is the result set: