What are ranking window functions in SQL?

Introduction

In SQL, ranking window functions assign a rank to each row in the result set based on specified criteria. It helps in determining the row’s relative position. These functions include RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(), and PERCENT_RANK().

Understanding ranking window functions

This section explores the core concepts and functionality of ranking window functions in SQL, providing insights into how these functions assign ranks and help in data analysis.

The ROW_NUMBER() function

The ROW_NUMBER() function assigns a unique row number to each row within a result set. It starts from 1 and increments by 1 for each subsequent row. It is important for various tasks, including ranking, data partitioning, and paging. ROW_NUMBER() assigns a unique number to each row without considering duplicates.

Check out the ROW_NUMBER() function Answer.

Let’s explore RANK() and DENSE_RANK(), which handle duplicate values differently.

The RANK() and DENSE_RANK() functions

The RANK() and DENSE_RANK() functions assign a rank to each row in the result set. In case of duplicate values, the RANK() function assigns the same rank to duplicate values and the next rank is calculated after incrementing the current rank value. On the other hand, the DENSE_RANK() function assigns the same value to all the duplicate values. The next unique value will get the next consecutive number as rank.

Check out the RANK() and DENSE_RANK() functions Answer.

The NTILE() function

The NTILE() function divides the result set into groups known as buckets or tiles. It then assigns a bucket number to each row based on the specified number within the function.

The PERCENT_RANK() function

The PERCENT_RANK() computes a relative ranking of the value based on the rows in a dataset. It returns a value ranging from 0 to 1 (inclusive). If there are duplicate values, PERCENT_RANK assigns the same percentile rank to each duplicate value, considering the highest ranking value has no ties.

SQL window function terminology

It is important to note that partitions and window frames can also be utilized with these ranking functions. SQL window functions allow us to perform calculations across any number of table rows that are linked to the current row. Window frames specify the range of rows within a partition, enabling more precise calculations. Partitions, which are defined using the PARTITION BY, clause allow us to group rows within the same partition together before applying the window function, providing even more control over our analysis. Check out the PARTITION BY clause Answer for more information.

Example database

We have some data on the student’s exam results in the Exam_result table that we can view by using the SELECT query.

SELECT * FROM Exam_result;

Practical examples of ranking functions

Let’s get more information on each ranking function in this section with practical coding examples.

The ROW_NUMBER() function

The ROW_NUMBER() function is useful when we need a unique integer for each row without considering duplicate values. The example, including the PARTITION BY clause, is given below:

SELECT std_name,
marks,
subject_name,
ROW_NUMBER() OVER (
PARTITION BY subject_name
ORDER BY marks DESC
) AS rank_by_row_number
FROM Exam_result;

The ranking function in the above code assigns unique values sequentially to each row in the window frame divided by a partition. The marks column is sorted in descending order; you can observe the output in ascending order by replacing DESC with ASC in the ORDER BY clause.

Check out the ORDER BY clause Answer for more information.

The RANK() and DENSE_RANK() functions

The RANK() function is useful when we want to leave gaps in the ranking sequence for duplicate values. On the other hand, the DENSE_RANK() is useful when we want to assign a unique rank to each row without gaps. The example, including the PARTITION BY clause, is given below:

SELECT std_name,
marks,
subject_name,
RANK() OVER(
PARTITION BY subject_name
ORDER BY marks DESC
) AS rank_by_rank,
DENSE_RANK() OVER(
PARTITION BY subject_name
ORDER BY marks DESC
) AS rank_by_dense_rank
FROM Exam_result

Note: We modified the records a bit in this example clarify the concept.

The ranking function in the above code assigns ranking values to each row in the window frame divided by a partition. The marks column is sorted in descending order; you can observe the output in ascending order by replacing DESC with ASC in the ORDER BY clause.

The NTILE() function

The NTILE(n) function is useful when we want to divide the result set into a specified number of groups (buckets). The example for NTILE() having a bucket size of 2 is given below:

SELECT std_name,
marks,
subject_name,
NTILE(2) OVER(
ORDER BY marks ASC
) AS rank_by_ntile_2
FROM Exam_result

The ranking function in the above code assigns bucket numbers as ranking values to each row. The marks column is sorted in ascending order; you can observe the output in descending order by replacing ASC with DESC in the ORDER BY clause.

The PERCENT_RANK() function

The PERCENT_RANK() function is useful when we need to calculate the relative rank of each row within the partition as a percentage. Its range is from 0 to 1.

SELECT std_name,
marks,
subject_name,
PERCENT_RANK() OVER(
PARTITION BY subject_name
ORDER BY marks DESC
) AS rank_by_percent_rank
FROM Exam_result

The ranking function in the above code assigns percentiles as ranking values to each row in the window frame divided by a partition. The marks column is sorted in descending order; you can observe the output in ascending order by replacing DESC with ASC in the ORDER BY clause. It behaves differently when there are duplicate or null values.

Conclusion

In conclusion, ranking window functions in SQL offer powerful tools for analyzing and processing data. These functions provide various ways to assign ranks, row numbers, and percentile ranks to rows in a result set. We saw each function with the coding examples. We learned how these functions work within partitions, and utilizing window functions can greatly enhance our data analysis capabilities in SQL.

Copyright ©2024 Educative, Inc. All rights reserved