The SQL Server
DENSE_RANK function is a ranking function that assigns a rank to each row in a result set, starting with 1 for the first row.
Unlike the RANK function, DENSE_RANK does not skip ranks when there are ties.
This can be useful when you want to know the relative position of rows within a result set.
DENSE_RANK() OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
DENSE RANK will assign the same rank to tied rows.
DENSE RANK, on the other hand, will not omit any digits. As a result, in the case of a tie for first place between the top two rows, each will receive a rank of 1.
In contrast to RANK, which would have given the third row a ranking of 3, this system assigns a rank of 2.
DENSE_RANK with OVER clause
Here is an example of how the DENSE_RANK function with OVER clause can be used to dense_rank rows in a query:
DENSE_RANK() OVER(ORDER BY price, name) AS DR,
DENSE_RANK with PARTITION BY clause
The following example shows how the DENSE_RANK function can be used with the PARTITION BY clause:
SELECT name, price,
DENSE_RANK() OVER (PARTITION BY price ORDER BY name) AS DR
DENSE_RANK with TOP
For example, the following query uses the DENSE_RANK function to return the top 5 ebooks by price:
SELECT TOP 5
DENSE_RANK() OVER(ORDER BY price DESC) AS DR,