SQL Reference - DENSE

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

Example

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) salary_rank
FROM employees;

Output

| name  | salary | salary_rank |
|-------|--------|-------------|
| John  | 70000  |      1      |
| Alice | 60000  |      2      |
| Bob   | 60000  |      2      |
| David | 50000  |      3      |
| Carol | 40000  |      4      |

Explanation

In the example, the DENSE_RANK() function is applied to the salary column in the employees table, which ranks employees based on their salary in descending order. Employees with same salary get the same rank. Unlike RANK(), DENSE_RANK() does not skip rank in case of ties, hence the rank follows orderly as 1, 2, 2, 3, 4, not 1, 2, 2, 4, 5.

DENSE_RANK() OVER ([PARTITION BY column_1, column_2,…] [ORDER BY column_1, column_2,… [ASC | DESC]], …)

Example

CREATE TABLE Rankings(
    employee VARCHAR(50),
    salary INT
);
INSERT INTO Rankings(employee, salary)
VALUES ('Amy', 50000), ('John', 60000), ('Bob', 70000), ('John', 60000), ('Amy', 70000);

SELECT employee, salary, DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM Rankings;

Output

 employee | salary | dense_rank
----------|--------|-----------
 Bob      |  70000 |         1
 Amy      |  70000 |         1
 John     |  60000 |         2
 John     |  60000 |         2
 Amy      |  50000 |         3

Explanation

In this example, DENSE_RANK function is used to rank the salaries of the employees in descending order. Unlike RANK, DENSE_RANK does not skip any ranks if there are rank ties. You can see that both Bob and Amy get a rank of 1 for having the highest salary. John, though having the same salary twice, is assigned the next rank, 2, and not 3. Amy’s lower salary is assigned the following rank, 3.

DENSE_RANK() OVER ([Partition By value_expression] Order By sort_expression [ASC | DESC])

Example

WITH Sales_Ranks AS
(
   SELECT
      SalesPerson,
      Revenue,
      DENSE_RANK() OVER (ORDER BY Revenue DESC) AS Rank
   FROM Sales
)
SELECT * FROM Sales_Ranks;

Output

SalesPerson	Revenue	Rank
John	10000	1
Anna	8000	2
Mark	8000	2
Emma	6000	3

#### Explanation

In the example, DENSE_RANK() function is used to assign rankings to each row in the Sales table. The rankings are based on the Revenue in descending order. DENSE_RANK() assigns the same ranks for the same values without skipping any ranks. Thus, the subsequent rank after 2 is 3, irrespective of the two '2' ranks for Anna and Mark's Revenue.

</Fragment>

<Fragment slot="tab-4">Oracle</Fragment>
<Fragment slot="panel-4">
#### DENSE\_RANK\(\) OVER \(\[PARTITION BY column\(s\)\] ORDER BY column\(s\)\)

#### Example
```sql
SELECT
  department_id,
  employee_id,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC
  ) salary_rank
FROM employees;

Output

| DEPARTMENT_ID | EMPLOYEE_ID | SALARY | SALARY_RANK |
|---------------|-------------|--------|-------------|
| 10            | 200         | 4400   | 1           |
| 20            | 201         | 13000  | 1           |
| 20            | 202         | 6000   | 2           |
| 30            | 114         | 11000  | 1           |
| 30            | 115         | 3000   | 2           |
| ...           | ...         | ...    | ...         |

Explanation

The provided SQL code assigns a rank for each employee within their department based on the salary. The DENSE_RANK() function is used to assign the ranking, where employees with higher salary get lower rank number. If two or more employees have the same salary, they receive the same rank, and the next rank is not skipped. The ranking is reset for each department due to the PARTITION BY clause.