SQL - DENSE_RANK ()

DENSE_RANK ()

RANK: This gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. Therefore, if you have 3 items at rank 2, the next rank listed will be ranked 5.

DENSE_RANK: This gives you the ranking within your ordered partition, but the ranks are consecutive in it. Also, no ranks are skipped if there are ranks with multiple items.

This function returns the rank of each row within a result set partition, with no gaps in the ranking values. The rank of a specific row is one plus the number of distinct rank values that come before that specific row.

DENSE_RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )

CREATE TABLE sales.dense_rank_demo ( v VARCHAR(10) ); INSERT INTO sales.dense_rank_demo(v) VALUES('A'),('B'),('B'),('C'),('C'),('D'),('E');

SELECT * FROM sales.dense_rank_demo;

SELECT v, DENSE_RANK() OVER ( ORDER BY v ) my_dense_rank, RANK() OVER ( ORDER BY v ) my_rank FROM sales.dense_rank_demo;

Here is the output: