Why ROW_NUMBER function in SQL? - Purpose & Use Cases

The Big Idea

What if you could number thousands of rows perfectly in seconds, without lifting a finger?

The Scenario

Imagine you have a long list of customer orders in a spreadsheet. You want to number each order by the date it was placed, so you can see which came first. Doing this by hand means scrolling through hundreds or thousands of rows, writing numbers next to each order, and making sure you don't skip or repeat any.

The Problem

Manually numbering rows is slow and tiring. It's easy to make mistakes like skipping a number or numbering out of order. If the list changes, you must redo all the numbering. This wastes time and causes frustration, especially with big data.

The Solution

The ROW_NUMBER function in SQL automatically adds a unique number to each row based on the order you choose. It does this instantly and perfectly, even if your data changes. This saves you from manual counting and errors.

Before vs After

✗ Before

Add a new column and type numbers manually for each row.

✓ After

SELECT order_id, order_date, ROW_NUMBER() OVER (ORDER BY order_date) AS row_num FROM orders;

What It Enables

With ROW_NUMBER, you can quickly rank, paginate, or organize data in any order without manual effort.

Real Life Example

A sales manager wants to see the top 10 most recent orders. Using ROW_NUMBER, they can number all orders by date and easily pick the first 10.

Key Takeaways

Manually numbering rows is slow and error-prone.

ROW_NUMBER automatically assigns numbers based on your chosen order.

This makes sorting, ranking, and paging data easy and reliable.

Practice

1. What does the ROW_NUMBER() function do in SQL?

easy

A. Counts the total number of rows in a table.

B. Filters rows based on a condition.

C. Calculates the sum of values in a column.

D. Assigns a unique sequential number to each row in the result set.

Solution

1. Step 1: Understand the purpose of ROW_NUMBER()

   The ROW_NUMBER() function assigns a unique sequential integer to rows in the result set, starting at 1.

2. Step 2: Compare with other options

   Counting rows, summing values, or filtering rows are done by other SQL functions like COUNT(), SUM(), or WHERE clause, not ROW_NUMBER().

3. Final Answer:

   Assigns a unique sequential number to each row in the result set. -> Option D

4. Quick Check:

   ROW_NUMBER() = unique row numbers [OK]

Hint: ROW_NUMBER() gives each row a unique number starting at 1 [OK]

Common Mistakes:

• Confusing ROW_NUMBER() with COUNT()
• Thinking ROW_NUMBER() filters rows
• Assuming ROW_NUMBER() sums values

2. Which of the following is the correct syntax to assign row numbers ordered by salary in descending order?

easy

A. SELECT ROW_NUMBER() FROM employees ORDER BY salary DESC;

B. SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees;

C. SELECT ROW_NUMBER() ORDER BY salary DESC FROM employees;

D. SELECT ROW_NUMBER(salary DESC) FROM employees;

Solution

1. Step 1: Identify correct ROW_NUMBER() syntax

   The correct syntax uses ROW_NUMBER() OVER (ORDER BY column) to assign numbers ordered by a column.

2. Step 2: Check each option

   Only SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; correctly uses OVER (ORDER BY salary DESC). The other options omit the OVER clause, pass arguments directly to ROW_NUMBER(), or misplace the ORDER BY clause.

3. Final Answer:

   SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees; -> Option B

4. Quick Check:

   ROW_NUMBER() needs OVER with ORDER BY [OK]

Hint: ROW_NUMBER() must use OVER() with ORDER BY inside [OK]

Common Mistakes:

• Omitting OVER() clause
• Placing ORDER BY outside OVER()
• Passing column directly to ROW_NUMBER()

3. Given the table sales with columns region and amount, what will this query return?

SELECT region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales;

medium

A. The total count of sales per region.

B. A unique row number for all sales ignoring region, ordered by amount ascending.

C. A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first.

D. The sum of amounts per region.

Solution

1. Step 1: Understand PARTITION BY effect

   Using PARTITION BY region restarts the row numbering for each region group separately.

2. Step 2: Understand ORDER BY inside ROW_NUMBER()

   Ordering by amount DESC means the highest amount in each region gets row number 1, next highest 2, and so on.

3. Final Answer:

   A unique row number for each sale, restarting at 1 for each region, ordered by highest amount first. -> Option C

4. Quick Check:

   ROW_NUMBER() with PARTITION BY = numbering per group [OK]

Hint: PARTITION BY restarts numbering per group [OK]

Common Mistakes:

• Ignoring PARTITION BY and numbering all rows globally
• Confusing ORDER BY direction
• Thinking ROW_NUMBER() counts rows

4. Identify the error in this query:

SELECT name, ROW_NUMBER() ORDER BY salary FROM employees;

medium

A. Missing OVER() clause after ROW_NUMBER().

B. ORDER BY cannot be used in SELECT clause.

C. ROW_NUMBER() cannot be used without GROUP BY.

D. salary column does not exist.

Solution

1. Step 1: Check ROW_NUMBER() syntax

   The ROW_NUMBER() function requires an OVER() clause specifying ordering or partitioning.

2. Step 2: Analyze the query

   The query uses ROW_NUMBER() without OVER(), which causes a syntax error. ORDER BY inside SELECT is invalid without OVER().

3. Final Answer:

   Missing OVER() clause after ROW_NUMBER(). -> Option A

4. Quick Check:

   ROW_NUMBER() must have OVER() [OK]

Hint: ROW_NUMBER() always needs OVER() clause [OK]

Common Mistakes:

• Forgetting OVER() after ROW_NUMBER()
• Placing ORDER BY outside OVER()
• Assuming ROW_NUMBER() works without ordering

5. You want to select the top 2 highest paid employees per department from the employees table. Which query correctly uses ROW_NUMBER() to achieve this?

hard

A. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2;

B. SELECT *, ROW_NUMBER() FROM employees WHERE salary IN (SELECT MAX(salary) FROM employees GROUP BY department);

C. SELECT department, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM employees WHERE rn <= 2;

D. SELECT department, salary FROM employees ORDER BY salary DESC LIMIT 2;

Solution

1. Step 1: Use ROW_NUMBER() with PARTITION BY department

   To get top 2 per department, assign row numbers restarting for each department ordered by salary descending.

2. Step 2: Filter rows with row number less than or equal to 2

   Wrap the query as a subquery and filter where rn <= 2 to get top 2 employees per department.

3. Final Answer:

   SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2; -> Option A

4. Quick Check:

   ROW_NUMBER() + PARTITION + filter = top N per group [OK]

Hint: Use ROW_NUMBER() with PARTITION BY and filter rn <= N [OK]

Common Mistakes:

• Not using PARTITION BY to group by department
• Filtering on rn in the same SELECT without subquery
• Using LIMIT without partitioning