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()`

.

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.

`ROW_NUMBER()`

functionThe `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.

`RANK()`

and `DENSE_RANK()`

functionsThe `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.

`NTILE()`

functionThe `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.

`PERCENT_RANK()`

functionThe `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 terminologyIt 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.

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;

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

`ROW_NUMBER()`

functionThe `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.

`RANK()`

and `DENSE_RANK()`

functionsThe `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.

`NTILE()`

functionThe `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.

`PERCENT_RANK()`

functionThe `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.

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

TRENDING TOPICS