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 thePARTITION BYclause 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_nameORDER BY marks DESC) AS rank_by_row_numberFROM 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_nameORDER BY marks DESC) AS rank_by_rank,DENSE_RANK() OVER(PARTITION BY subject_nameORDER BY marks DESC) AS rank_by_dense_rankFROM 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_2FROM 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_nameORDER BY marks DESC) AS rank_by_percent_rankFROM 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.
Free Resources
