SQL Tutorial
•
•
4 min read
RANK(), DENSE_RANK(), and ROW_NUMBER() in SQL – How to Think and Solve Questions Step by Step
RANK(), DENSE_RANK(), and ROW_NUMBER() in SQL – How to Think and Solve Questions Step by Step
Hey, I’m Gaurav.
When I first saw ranking questions in SQL interviews, I was completely confused.
The interviewer asked:
"Rank the employees by salary within each department."
My noob brain immediately panicked:
Brute Force / Noob Way (What most beginners do):
- Sort the data manually in my mind
- Try to use GROUP BY (but then I lose individual rows)
- Write complicated self-joins with subqueries
- End up with a messy, slow query that barely works
I used to think ranking was some advanced black magic.
Today, I’m going to show you exactly how I learned to think about `RANK()`, `DENSE_RANK()`, and `ROW_NUMBER()` — from confused beginner to confidently solving them in interviews.
We’ll use our familiar `employees` table. You can practice every question live here: [sqlpractice.in](https://sqlpractice.in)
Let’s break it down step by step.
First, Understand the Core Problem
Ranking questions usually ask:
"Give each row a number based on some order, and reset the numbering for every group."
Without window functions, we try to do it the hard way (subqueries, joins, temp tables) — which is slow and complicated.
The breakthrough comes when you realize:
> Window functions let you assign ranks while keeping all rows in the result.
The Three Main Ranking Functions
All three use the same basic syntax:
```sql
FUNCTION() OVER (PARTITION BY group_column ORDER BY sort_column)
```
But they behave differently when there are ties (same values).
1. ROW_NUMBER()
What it does: Assigns a unique sequential number to each row.
No gaps. No ties allowed — if values are same, it still gives different numbers (arbitrary order).
Best for: When you want unique row numbers (pagination, removing duplicates, etc.)
Example:
```sql
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees;
```
Practice Question 1 (Easy)
Assign a unique row number to every employee, ordered by salary descending.
[Try it live →](https://sqlpractice.in/?question=row-number-basic)
2. RANK()
What it does: Assigns the same rank to tied values, then skips the next ranks.
Example: If two people have the same salary and both get rank 1, the next person gets rank 3 (skips 2).
Best for: Competition-style ranking where ties should affect the next position.
Example:
```sql
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_with_gaps
FROM employees;
```
Practice Question 2 (Medium)
Rank employees by salary within each department using RANK().
[Try it live →](https://sqlpractice.in/?question=rank-function)
3. DENSE_RANK()
What it does: Assigns the same rank to tied values, but does not skip the next ranks.
Example: If two people tie for rank 1, the next person still gets rank 2.
Best for: When you want continuous ranking without gaps.
Example:
```sql
SELECT
name,
department,
salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
```
Practice Question 3 (Medium)
Rank employees by salary using DENSE_RANK() and compare with RANK().
[Try it live →](https://sqlpractice.in/?question=dense-rank)
How to Choose Between RANK, DENSE_RANK, and ROW_NUMBER (My Thinking Framework)
When you see a ranking question, ask yourself these questions in order:
1. Do I need unique numbers for every row? → Use `ROW_NUMBER()`
2. Do I want ties to affect the next rank (gaps)? → Use `RANK()`
3. Do I want continuous ranking without gaps? → Use `DENSE_RANK()`
Real interview tip:
Most companies prefer `DENSE_RANK()` when they say "rank" because they don’t want gaps in ranking.
Advanced Example: Top 3 Salaries in Each Department
Question: Find the top 3 highest paid employees in each department.
Noob approach: Try to use LIMIT inside GROUP BY (doesn’t work)
Better thinking:
1. I need to rank salaries within each department.
2. Then filter only the top 3 ranks.
Correct Solution:
```sql
SELECT
FROM (
SELECT
name,
department,
salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
) ranked
WHERE rank <= 3;
```
Practice Question 4 (Hard)
Find the top 3 highest paid employees in each department.
[Try it live →](https://sqlpractice.in/?question=top-n-per-group)
Practice Question 5 (Expert)
Show employees with their salary and the difference from the highest salary in their department.
Final Thoughts
I used to fear ranking questions.
Now I see them as one of the easiest once you understand the thinking process:
- ROW_NUMBER() → unique sequential numbers
- RANK() → ties with gaps
- DENSE_RANK() → ties without gaps
The key is: Don’t try to write the perfect query immediately.
First decide what kind of ranking you need, then apply the right function with proper `PARTITION BY` and `ORDER BY`.
Go practice all these ranking questions on [sqlpractice.in](https://sqlpractice.in) — the live editor will show you instantly whether your approach is correct.
Next article: Types of Views in SQL
Keep practicing — one concept at a time!
Building sqlpractice.in to help everyone master SQL for interviews — completely free.