ROW_NUMBER, RANK, DENSE_RANK in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ROW_NUMBER() function do in SQL?

ROW_NUMBER() assigns a unique sequential number to each row in the result set, starting at 1, without skipping any numbers, even if there are ties.

intermediate

How does RANK() differ from ROW_NUMBER() when there are ties?

RANK() assigns the same rank to tied rows but skips the next ranks accordingly. For example, if two rows tie for rank 1, the next rank will be 3, not 2.

intermediate

Explain DENSE_RANK() in simple terms.

DENSE_RANK() assigns the same rank to tied rows like RANK(), but it does not skip any ranks after ties. So if two rows tie for rank 1, the next rank will be 2.

beginner

Write a SQL query using ROW_NUMBER() to number employees by salary descending.

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

intermediate

When should you use DENSE_RANK() instead of RANK()?

Use DENSE_RANK() when you want to rank rows with ties but keep the ranks consecutive without gaps, which is useful for compact ranking lists.

What will ROW_NUMBER() assign to tied rows?

A Same number for all tied rows

B Ranks without gaps after ties

C Ranks with gaps after ties

D Unique sequential numbers without gaps

ROW_NUMBER() always assigns unique sequential numbers to each row, even if rows have the same values.

If two rows tie for rank 1, what rank does RANK() assign to the next row?

A 2

B 3

C 1

D 4

RANK() skips ranks after ties, so after two rows tied at 1, the next rank is 3.

Which function does NOT skip ranks after ties?

A ROW_NUMBER()

B RANK()

C DENSE_RANK()

D COUNT()

DENSE_RANK() assigns the same rank to ties but does not skip ranks after them.

Which function assigns unique numbers regardless of ties?

A ROW_NUMBER()

B DENSE_RANK()

C RANK()

D SUM()

ROW_NUMBER() always assigns unique sequential numbers to each row.

What is the main use of RANK() in SQL?

A To assign ranks with gaps after ties

B To count rows

C To assign unique row numbers

D To sum values

RANK() assigns ranks to rows and skips ranks after ties, creating gaps.

Describe the difference between ROW_NUMBER(), RANK(), and DENSE_RANK() with examples.

Think about how each function handles ties and numbering.

Explain a real-life scenario where you would use DENSE_RANK() instead of RANK().

Consider situations where you want a clean list of ranks.