ROW_NUMBER function in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROW_NUMBER() function do in SQL?

It assigns a unique sequential number to each row within a result set, starting at 1 for the first row.

intermediate

How do you reset the row numbering for each group in SQL using ROW_NUMBER()?

Use the PARTITION BY clause inside ROW_NUMBER() to restart numbering for each group.

beginner

Write a simple SQL snippet using ROW_NUMBER() to number rows ordered by salary descending.

SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank FROM employees;

intermediate

Can ROW_NUMBER() produce ties (same number for multiple rows)? Why or why not?

No, ROW_NUMBER() always gives unique numbers even if values are the same, unlike RANK() which can produce ties.

beginner

What happens if you omit ORDER BY in the ROW_NUMBER() OVER() clause?

The query will fail because ORDER BY is required to define the order for numbering rows.

What does ROW_NUMBER() assign to each row?

A A unique sequential number starting at 1

B The total count of rows

C The sum of values in a column

D The average value of a column

ROW_NUMBER() assigns a unique sequential number to each row starting at 1.

Which clause is used with ROW_NUMBER() to restart numbering for each group?

A GROUP BY

B HAVING

C ORDER BY

D PARTITION BY

PARTITION BY restarts the row numbering for each group in ROW_NUMBER().

If two rows have the same value in the ORDER BY column, what does ROW_NUMBER() do?

A Assigns the same number to both rows

B Assigns different unique numbers to each row

C Skips numbering those rows

D Returns an error

ROW_NUMBER() always assigns unique numbers even if values are the same.

What is required inside the OVER() clause for ROW_NUMBER() to work?

A ORDER BY clause

B GROUP BY clause

C WHERE clause

D HAVING clause

ORDER BY is required inside OVER() to define the order for ROW_NUMBER().

Which function can produce ties in numbering rows?

A ROW_NUMBER()

B COUNT()

C RANK()

D SUM()

RANK() can assign the same rank to rows with equal values, producing ties.

Explain how ROW_NUMBER() works and how you can restart numbering for each group.

Think about numbering rows like giving each person a unique ticket number, and restarting numbering for each team.

Describe the difference between ROW_NUMBER() and RANK() functions.

Consider how a race ranking might handle ties versus unique positions.