How to Use DENSE_RANK in SQL: Examples and Use Cases

With 423 recognized DBMS systems in play, zettabytes of data are created, captured, copied, and consumed every day—primarily through SQL queries. Managing this vast amount of data requires specialized job functions, one of which is ranking. Different types of rankings require different SQL functions.

Imagine you’re responsible for assigning ranks in a highly competitive running race. The rules are simple: if two or more people finish at the same time (a tie), they receive the same rank. The next rank is the very next number, with no skips.

For example, Alice and Bob, the top contenders, both finish first and share first place, or let’s say rank 1. Charlie finishes next, earning rank 2. Finally, Dave comes in after Charlie, receiving rank 3. Notice how the ranks are dense—there are no gaps between the numbers.

In SQL querying, DENSE_RANK is ideal for situations where you want rankings without skipped numbers, such as creating leaderboards, identifying top-performing groups, or sorting data into tiers.

For instance, imagine a table employees with the following data:

Employee_ID	Department	Salary
1	Sales	70000
2	Sales	70000
3	Sales	65000
4	IT	80000
5	IT	75000
6	IT	75000

You can run a query (discussed in detail later) with DENSE_RANK to rank employees by salary within each department:

SELECT
Department,
Employee_ID,
Salary,
DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM employees;

Which will result in:

Result:

Department	Employee_ID	Salary	Rank
Sales	1	70000	1
Sales	2	70000	1
Sales	3	65000	2
IT	4	80000	1
IT	5	75000	2
IT	6	75000	2

Notice how employees 1 and 2 in Sales have the same salary, thus sharing rank 1, while employee 3 in Sales has a lower salary with a rank of 2. In IT, the ranks adjust similarly without any gaps.

Here is a table comparing DENSE_RANK with other rank functions that we mentioned:

Function	Behavior
DENSE_RANK	Assigns consecutive ranks with no gaps for ties.
RANK	Leaves gaps in the ranking sequence for tied values.
ROW_NUMBER	Assigns a unique rank to each row, even if values are tied.

With the basics out of the way, let’s now understand the syntax of DENSE_RANK, and if you’re preparing for an interview, check out our SQL Learning Path and interview questions for further practice.

The syntax for DENSE_RANK is as follows:

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

TheDENSE_RANK() function calculates the rank of each row in the result set. TheOVER clause defines how the ranking is applied. Under the OVER clause, PARTITION BY groups rows into subsets where the ranking is restarted. As it’s optional, if omitted, ranking is applied across the entire result set. Another optional component, ORDER BY, determines the sorting order of rows within each partition or the whole dataset.

Basic Example

Let’s assume we have a table named sales with the following data:

Salesperson	Region	Sales
Alice	East	5000
Bob	East	5000
Charlie	East	4000
Dave	West	6000
Eve	West	6000
Frank	West	5500

Here’s how we can use DENSE_RANK to rank salespeople within each region based on their sales:

SELECT
    Region,
    Salesperson,
    Sales,
    DENSE_RANK() OVER (PARTITION BY Region ORDER BY Sales DESC) AS Rank
FROM sales;

Result:

Region	Salesperson	Sales	Rank
East	Alice	5000	1
East	Bob	5000	1
East	Charlie	4000	2
West	Dave	6000	1
West	Eve	6000	1
West	Frank	5500	2

Within the East region, Alice and Bob share rank 1 because their sales are tied at 5000. Charlie comes next with rank 2. Similarly, in the West region, Dave and Eve share rank 1, and Frank is ranked 2.

Here are some practical examples of DENSE_RANK that may come in handy while querying:

1. Ranking Students by Scores

Scenario: A school needs to rank students based on their exam scores, with ties allowed.

Query:

SELECT
    student_id,
    student_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Result:

Student_ID	Student_Name	Score	Rank
101	Alice	95	1
102	Bob	95	1
103	Charlie	90	2
104	Dave	85	3

Explanation:

• Alice and Bob share rank 1 because their scores are tied.
• Charlie receives rank 2, and Dave gets rank 3.

2. Ranking Products by Sales in Categories

Scenario: A retailer wants to rank products by their total sales within each category.

Query:

SELECT
    category,
    product_name,
    total_sales,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY total_sales DESC) AS rank
FROM products;

Result:

Category	Product_Name	Total_Sales	Rank
Electronics	TV	1000	1
Electronics	Laptop	800	2
Furniture	Sofa	1200	1
Furniture	Chair	1200	1

Explanation:

• Within the Electronics category, products are ranked based on sales, with no ties.
• In the Furniture category, Sofa and Chair tie for rank 1 because they have the same sales.

---

3. Employee Performance Rankings by Department

Scenario: A company evaluates employees’ performance by department and ranks them based on their scores.

Query:

SELECT
    department_id,
    employee_name,
    performance_score,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY performance_score DESC) AS rank
FROM employees;

Result:

Department_ID	Employee_Name	Performance_Score	Rank
HR	Sarah	95	1
HR	John	90	2
IT	Alice	98	1
IT	Bob	98	1

Explanation:

• Each department’s employees are ranked independently, with Sarah and John ranked in HR and Alice and Bob sharing rank 1 in IT.

4. Generating a Sports Leaderboard

Scenario: A sports league ranks players based on their scores in a tournament.

Query:

SELECT
    player_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM tournament_results;

Result:

Player_Name	Score	Rank
Mike	300	1
Tom	300	1
Jerry	290	2
Steve	280	3

Explanation:

• Mike and Tom tie for rank 1, while Jerry and Steve follow sequentially without gaps in ranking.

5. Identifying Top Customers by Region

Scenario: A business wants to identify the top-spending customers in each region.

Query:

SELECT
    region,
    customer_name,
    total_spent,
    DENSE_RANK() OVER (PARTITION BY region ORDER BY total_spent DESC) AS rank
FROM customers;

Result:

Region	Customer_Name	Total_Spent	Rank
North	Alice	5000	1
North	Bob	4500	2
South	Charlie	6000	1
South	Dave	6000	1

Explanation:

• Rankings reset for each region, and customers with the same spending (e.g., Charlie and Dave) share the same rank.

6. Detecting Duplicates in a Dataset

Scenario: A data analyst needs to identify duplicates based on a combination of columns.

Query:

SELECT
    customer_id,
    order_id,
    product_id,
    DENSE_RANK() OVER (PARTITION BY customer_id, product_id ORDER BY order_id) AS rank
FROM orders;

Result:

Customer_ID	Order_ID	Product_ID	Rank
1	1001	A	1
1	1002	A	2
2	2001	B	1

Explanation:

• Within each customer_id and product_id group, rows are ranked by order_id. Duplicate entries can be identified by ranks higher than 1.

Using DENSE_RANK in SQL queries is a straightforward way to rank data without gaps, even when ties occur. It’s especially helpful in scenarios like ranking sales, scores, or performances within groups or across entire datasets. By incorporating it into your queries with PARTITION BY and ORDER BY clauses, you can organize data meaningfully and extract insights efficiently. Mastering DENSE_RANK allows you to handle complex ranking tasks with simple and effective SQL queries.

All the best!