An Easy Guide to SQL Ranking Window Functions

By /
Featured

While writing SQL queries, we perform multiple calculations on data using various aggregated functions and we often need to rank our data for better understanding and analysis of the databases. Ranking functions are one of the important types of window functions which can be easily implemented. In this tutorial, we will learn about the multiple rank functions with practical examples.

Understanding RANK Function

The RANK() window function specifies a rank for each row within a partition of a result set. The rank of the first row in a partition is one. The RANK() function adds the number of tied rows to the tied rank to determine the rank of the next row, therefore there are chances that the ranks may not be sequential.

Syntax:

RANK() OVER(
PARTITION BY partition_exp
ORDER BY sort_exp );Code language: SQL (Structured Query Language) (sql)

Where,

  • PARTITION BY: Divides the rows of the result set partitions, where the function is applied
  • ORDER BY: Specifies the logical sorting order of the rows in each partition, where the function is applied

Also Read: Window Functions VS Aggregate Functions in MySQL

Types of SQL Ranking Functions

We have the following Window Rank functions:

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

We will use the below Result table to understand the rank functions better:

Result
Result

1. ROW_NUMBER()

The ROW_NUMBER() Ranking function is used to get a sequential number for each row specified in the data. It gives the rank of one to the first row and then increases the value to one for each row. We obtain different ranks for rows with common values as well.

Syntax:

ROW_NUMBER() 
OVER(PARTITION BY partition_expression
ORDER BY sort_expression)Code language: SQL (Structured Query Language) (sql)

Example:

To fetch a unique sequential number for each row in the specified data from the Result table, we will use the ROW_NUMBER() SQL RANK function.

SELECT StudentName, Subject, Marks,
ROW_NUMBER() OVER(ORDER BY Marks) RowNumber
From Result;Code language: SQL (Structured Query Language) (sql)

Output:

ROW_NUMBER
ROW NUMBER

In the above table, we can see that it sorts the data in ascending order and assigns the rank for each row.

2. RANK()

The RANK() Ranking function is used to specify the rank of each row in the result set. It is possible to get duplicate value rankings within a partition using the RANK() function and also to get non-sequential rankings.

Syntax:

RANK()
OVER(PARTITION BY partition_expression
ORDER BY sort_expression)Code language: SQL (Structured Query Language) (sql)

Example:

To fetch the rank of the results of students as per their marks in the subjects from the Result table, we will use the RANK() SQL RANK function.

SELECT StudentName, Subject, Marks,
RANK() OVER(ORDER BY Marks DESC) Rank
From Result
ORDER BY Rank;Code language: SQL (Structured Query Language) (sql)

Output:

RANK
RANK

In the above table, we can see each student gets ranked as per their marks irrespective of the subject.

3. DENSE_RANK()

The DENSE_RANK() Ranking function is used to assign a unique number within the partition based on the specified column value. It is kind of similar to the Rank function, with a minor change. If there are duplicate values in the SQL DENSE_RANK() function, SQL assigns different rankings to those rows. We get the same rank for similar values.

Syntax:

DENSE_RANK()
OVER(PARTITION BY partition_expression
ORDER BY sort_expression)Code language: SQL (Structured Query Language) (sql)

Example:

SELECT StudentName, Subject, Marks,
DENSE_RANK() OVER(ORDER BY Marks DESC) Rank
From Result
ORDER BY Rank;Code language: SQL (Structured Query Language) (sql)

Output:

DENSE RANK
DENSE RANK

NOTE: The DENSE_RANK() function, maintains the rank and doesn’t give any gap for the values whereas the RANK() function assigns a gap in the output.

4. NTILE()

The NTILE() Ranking function is used to divide the rows within a partition into the number of specified divisions. The rows are then specified to one of these groups based on their rank starting with 01 and continuing until the number of groups is specified.

Syntax:

NTILE()
OVER(PARTITION BY partition_expression
ORDER BY sort_expression)Code language: SQL (Structured Query Language) (sql)

Example:

Let’s divide the students into two groups based on their marks, where each group have a roughly equal number of students.

SELECT StudentName, Subject, Marks,
NTILE(2) OVER(ORDER BY Marks DESC) Rank
From Result
ORDER BY Rank;Code language: SQL (Structured Query Language) (sql)

Output:

NTILE
NTILE

In the above, we can see that the NTILE() function assigns a group number to each row.

Conclusion

In this tutorial, we have learned about SQL RANK functions. Also, we have seen how they differ from each other with practical examples. While exploring and manipulating the data, it is important for a SQL developer to be familiar with these functions. We hope you enjoyed the tutorial.

Reference

https://stackoverflow.com/questions/12739208/how-to-use-rank-in-sql-server