The DENSE_RANK() function is an analytic (window) function in Oracle SQL that assigns ranks to rows in an ordered set without gaps in ranking values, even when there are ties.

It is especially useful when you want to generate rankings with continuous numbers, even for rows with duplicate values.

1. Syntax

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

Parameters:

• PARTITION BY (optional): Divides the data into groups. Ranking restarts within each group.
• ORDER BY: Defines how rows are ranked.
• ASC or DESC: Rank in ascending or descending order.

2. Key Features

• Ties (equal values) receive the same rank.
• No gaps in ranks (unlike RANK()).
• Always starts from 1.
• Works as a non-aggregating analytic function: It does not remove rows or change result size.

3. Example Table: employees

CREATE TABLE employees (
  employee_id NUMBER,
  name VARCHAR2(50),
  department_id NUMBER,
  salary NUMBER
);

INSERT INTO employees VALUES (1, 'Alice', 10, 5000);
INSERT INTO employees VALUES (2, 'Bob', 10, 7000);
INSERT INTO employees VALUES (3, 'Charlie', 20, 6000);
INSERT INTO employees VALUES (4, 'David', 20, 6000);
INSERT INTO employees VALUES (5, 'Eve', 10, 7000);
COMMIT;

4. Basic Usage: Ranking by Salary

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

Output:

NAME	SALARY	RANK
Bob	7000	1
Eve	7000	1
Charlie	6000	2
David	6000	2
Alice	5000	3

Explanation:

• Bob and Eve have the highest salary, so both get rank 1.
• Charlie and David share rank 2.
• Alice gets rank 3.
• No gaps in rank numbers (no missing 2 between 1 and 3).

5. DENSE_RANK with PARTITION BY

Let’s say you want to rank employees within each department.

SELECT department_id, name, salary,
       DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
       ) AS dept_rank
FROM employees;

Output:

DEPARTMENT_ID	NAME	SALARY	DEPT_RANK
10	Bob	7000	1
10	Eve	7000	1
10	Alice	5000	2
20	Charlie	6000	1
20	David	6000	1

Explanation:

• Ranking restarts in each department.
• Duplicate salaries within a department get the same rank.

6. DENSE_RANK vs RANK vs ROW_NUMBER

Function	Duplicates Get Same Rank	Skips Ranks	Continuous Ranks	Use Case
RANK()	Yes	Yes	No	Leaderboards with ties
DENSE_RANK()	Yes	No	Yes	Ordered ranks with no gaps
ROW_NUMBER()	No	No	Yes	Unique row IDs (no duplicates)

Example with all three:

SELECT name, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

Output:

NAME	SALARY	RANK	DENSE_RANK	ROW_NUM
Bob	7000	1	1	1
Eve	7000	1	1	2
Charlie	6000	3	2	3
David	6000	3	2	4
Alice	5000	5	3	5

7. Real-World Use Case: Top 2 Salaries per Department

SELECT *
FROM (
  SELECT department_id, name, salary,
         DENSE_RANK() OVER (
             PARTITION BY department_id
             ORDER BY salary DESC
         ) AS rnk
  FROM employees
)
WHERE rnk <= 2;

Output:

DEPARTMENT_ID	NAME	SALARY	RNK
10	Bob	7000	1
10	Eve	7000	1
10	Alice	5000	2
20	Charlie	6000	1
20	David	6000	1

8. Notes and Best Practices

• DENSE_RANK() is best used when you want consecutive ranks without gaps.
• Works well for leaderboards, prize allocations, or filtering top-N records per group.
• Always combine with ORDER BY inside OVER() clause.
• Use PARTITION BY to restart ranking for groups (e.g., departments, categories).

9. Summary

Feature	Value
Function Type	Analytic (Window)
Handles Ties	Yes
Rank Skipping	No (ranks are dense)
Default Start Rank	1
Partitioning	Optional, for group-wise ranking

10. Conclusion

The DENSE_RANK() function in Oracle SQL is a powerful analytic function when:

• You need ranked output with no gaps.
• You want to find top-N rows per group.
• You are working with sorted data with duplicates and want fair ranking.

It’s a must-know tool for data analysis, reporting, and real-time ranking logic in Oracle SQL.