Text copied!
DENSE_RANK function
In SQL, the DENSE_RANK function assigns a rank to each row in a result set, with no gaps in the ranking values.
If multiple rows have the same value, they get the same rank, and the next rank is incremented by 1, without any gaps.
The syntax of the DENSE_RANK function generally looks like this :
SELECT
column1,
column2,
column3,
DENSE_RANK() OVER (PARTITION BY partition_column ORDER BY order_column [ASC | DESC]) AS dense_rank_column
FROM
table_name;
• PARTITION BY: This is an optional clause that divides the result set into partitions. The ranking will be calculated separately for each partition.
• ORDER BY: This clause specifies the column(s) based on which the ranking is determined. You can specify multiple columns for tie-breaking.
Here's an example of how you might use the DENSE_RANK function :
1. Let's assume we have a table named "[Student]".
2. Let's assume you want to assign a rank to the students based on their score.
3. Run below SQL statement :
SELECT
Student_Name,
Score,
DENSE_RANK() OVER (ORDER BY Score DESC) AS ScoreRank
FROM Student;
4. In the above statement, student with the highest score are assigned a rank of 1, and so on. Notice that students with the same score are given consecutive ranks without any gaps, which is the characteristic of the DENSE_RANK function.
Overall, the DENSE_RANK() function is a versatile tool in SQL that facilitates various analytical tasks by providing ordered rankings within result sets, handling ties gracefully, and enabling partitioned ranking for deeper data analysis.
Remember :
Using this function in a select statement won't modify the [Student] table directly, but it will only be reflected in the select statement's output.