This blog explains the usage of RANK window function in SQL Server. RANK function puts a rank number for each row depending on the group if partition clause is defined, followed by ordering the data defined. Its bit confusing but with an example, it can be easily understood. We are going to use the same sample set that we used for ROW_NUMBER() tutorial, so please refer the post to get the sample DDL and DML.

Syntax for RANK function

RANK()OVER(PARTITION BY column_list ORDER BY column_list ASC/DESC)

Step 1: The data is first grouping based on partition column list.

Step 2: As per the order column list the rank is applied on the grouped data from the Step 1.

If you have tie on order column, then the same number will repeat, however, the missing number will never be allocated to any rows later.

Simple Form of generating a RANK number

Rank number on grouping

Hope this is helpful to understand RANK window function in SQL Server.

Hope you enjoyed this post, share your feedback in comment section. Recommending to go through “Microsoft SQL Server – Beginners Guide” series for more information.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!