In this article, we will learn how to use the DENSE_RANK() function with a SQL select query.
The DENSE_RANK() is a window function that returns a unique rank number for each distinct row within a partition of a result set, with no gaps in ranking values.
It starts with 1 for the first row in each partition and the same column values receive the same ranks. Unlike the RANK() function, the DENSE_RANK() function is used to return consecutive rank values.
Syntax
Select column_name(s),
DENSE_RANK() OVER([PARTITION BY expression(s)] ORDER BY column(s))
From TABLE_NAME;
Note: The DENSE_RANK() function must have an OVER clause.
OVER Clause
The OVER clause is used to determine which rows from the query are applied to the function, what order they are evaluated in by that function, and when the function’s calculations should restart.
PARTITION BY Clause
The PARTITION BY is a subclause of the OVER clause. The PARTITION BY clause is used to divide a query’s result set into partitions. The DENSE_RANK() function is applied to each partition separately and reinitialize the row number for each partition.
Example-1 (with using PARTITION BY)
Select *,
DENSE_RANK() OVER(PARTITION BY age ORDER BY age) as Dense_Rank
From StudentInfo;
Output:
Here, the PARTITION BY with DENSE_RANK() function has no special meaning, as the rank will be done according to Student’s age values per each partition, and the data will be partitioned according to the Student’s age values.
Example-2 (without using PARTITION BY)
Select *,
RANK() OVER(ORDER BY age desc) as Rank,
DENSE_RANK() OVER(ORDER BY age desc) as Dense_Rank
From StudentInfo;
Output:
Also, check How To Use RANK() Function In SQL
