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.

---

Would you like me to write the next article ("Types of Views in SQL") in the same style?

Or do you want any changes in this ranking article before publishing?