RANK() Function in SQL Server

The RANK() function is a powerful window function in SQL Server used to assign a rank to each row within a result set. It is particularly useful when we need to assign a rank to a group of rows based on some sorting criteria and want to differentiate between rows that have the same values.

Unlike other ranking functions, the RANK() function introduces gaps in the ranking sequence when two or more rows have the same values, making it ideal for scenarios where you need rank differentiation, even for identical values.

What is the RANK() Function in SQL Server?

The RANK function in SQL server is used to assign rank to each row in a result set, based on a given ordering of data. The same rank is assigned to the rows which have the same values. The ranks in the RANK() function may not be consecutive because it assigns the same rank to rows with identical values.

After assigning the rank to the duplicate rows, the next rank is calculated by skipping the number of ranks that correspond to the duplicated rows, resulting in gaps in the ranking sequence. For example, if two rows are ranked 1st, the next row will be ranked 3rd (not 2nd).

Syntax:

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

Key Terms

• PARTITION BY: Divides the result set into partitions to which the RANK() function will be applied. If not specified, the entire result set is treated as a single partition.
• ORDER BY: Specifies the order in which the rows will be ranked. We can order the rows in ascending (ASC) or descending (DESC) order.
• The function returns an integer representing the rank of each row within the partition.

Example of the RANK()

To better understand how the RANK() function works in SQL Server, let's walk through an example using a table named geek_demo. In this example, we'll assign ranks to rows based on the values in the Name column.

1. Creating the Table

Let's create a demo table named geek_Demo to understand how the RANK() function works. 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

2. Using the RANK() Function

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

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

Output

Explanation: The RANK() function assigns the same rank (2) to both 'B' rows. The next rank (4) is given to 'C', with a gap in between, because there were two rows with rank 2.

RANK() vs DENSE_RANK() vs ROW_NUMBER()

Below are the comparison of the three common ranking functions in SQL Server: RANK(), DENSE_RANK(), and ROW_NUMBER().

• RANK(): Assigns the same rank to rows with duplicate values but introduces gaps in the ranking sequence for subsequent rows.
  
• DENSE_RANK(): Similar to RANK(), but does not leave gaps in the ranking sequence for rows with the same value. If two rows are ranked 1st, the next row will be ranked 2nd (not 3rd).
  
• ROW_NUMBER(): Always assigns a unique sequential number to each row, without any gaps or handling of duplicates.

When to Use the RANK() Function

The RANK() function is ideal for scenarios where:

• We need to assign ranks to items based on some criteria (e.g., sales figures, test scores).
• There are multiple rows with the same values and we want to give them the same rank but still preserve rank gaps.
• We want to identify top N items in each partition, with possible gaps for ties.

Important Points About SQL RANK Function

1. Window Function: The RANK() function is a window function, meaning it performs calculations across a set of table rows that are related to the current row. It works with PARTITION BY and ORDER BY to define the subset of data and the order in which ranks are assigned.

2. Handling Duplicates: When there are duplicate values in the data, RANK() assigns the same rank to the duplicate rows, and then the subsequent row will have a rank incremented by the number of duplicates. This results in gaps in the rank sequence.

3. Use in Ordering: The RANK() function allows for efficient ranking of rows based on certain columns, making it useful for generating reports and handling data that needs sorting or ranking (e.g., sales reports, exam scores, etc.).

4. Partitioning: The PARTITION BY clause in the RANK() function allows you to apply the ranking within a specific partition of data. For example, you could rank employees within each department or rank students within each class.

Conclusion

The RANK() function in a SQL Server is a versatile tool for assigning ranks to rows in a result set. Its ability to handle duplicates and work within partitions makes it a valuable feature for ranking and trend analysis. By understanding how to use RANK(), DENSE_RANK(), and ROW_NUMBER(), you can effectively organize and analyze data in SQL. By mastering the RANK() function and its counterparts like DENSE_RANK() and ROW_NUMBER(), we can enhance our SQL proficiency for both database management and data analytics.