Level 2 · ROW_NUMBER vs RANK vs DENSE_RANK

The three ranking functions agree until there is a tie. Then they disagree in ways that matter. You write them the same way:

SELECT
  name,
  unit_price,
  ROW_NUMBER() OVER (ORDER BY unit_price DESC, product_id) AS row_num,
  RANK()       OVER (ORDER BY unit_price DESC)             AS price_rank,
  DENSE_RANK() OVER (ORDER BY unit_price DESC)             AS dense_price_rank
FROM products
WHERE category = 'Lighting';

There is no PARTITION BY here, so the whole result is one window: every Lighting product is ranked against every other. The WHERE runs first, so only Lighting products reach the window.

These are the top Lighting prices at Harbor & Pine:

unit_price	ROW_NUMBER	RANK	DENSE_RANK
409.00	1	1	1
379.00	2	2	2
349.00	3	3	3
349.00	4	3	3
329.00	5	5	4
…
189.00	9	9	8
189.00	10	9	8
189.00	11	9	8
159.00	12	12	9

• ROW_NUMBER gives every row a unique number. Ties are broken arbitrarily unless you add a tie-breaker to the ORDER BY.
• RANK gives tied rows the same rank, then skips: after two products tied at 3 comes 5. It is the “Olympic” ranking: two golds means no silver.
• DENSE_RANK gives tied rows the same rank and does not skip: after two tied at 3 comes 4.

Which one to use

question	function
Exactly N rows per group, or pick one row per key (dedupe)	ROW_NUMBER
Top N including everyone tied at the cutoff	RANK (WHERE rnk <= N)
The Nth highest distinct value (“second highest salary”)	DENSE_RANK (WHERE drnk = N)

For a price badge like Ashley’s, DENSE_RANK reads most naturally (“2nd highest price point”), while RANK answers “how many products are pricier than this one, plus one”. Neither is wrong; they answer different questions, which is the point to make to the developer.

The second highest value

“Find the second highest salary” is a famous interview question. The DENSE_RANK answer handles ties and generalizes to any N:

WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
  FROM employees
)
SELECT DISTINCT salary
FROM ranked
WHERE drnk = 2;

Using ROW_NUMBER here would return the highest salary again if two people share the top salary. Using ORDER BY salary DESC LIMIT 1 OFFSET 1 has the same bug.

Ties are real

Prices like $189.00 repeat across products, salaries repeat within departments, order counts repeat across customers. Assume ties exist and decide how to treat them, rather than discovering them when a “top 5” list has six names in one tool and five in another.

NULLs in the ORDER BY

Ranking functions follow the sort’s NULL placement (chapter 3). In DuckDB, NULLs sort last by default, so they get the last ranks; in Postgres, ORDER BY x DESC puts NULLs first, so a NULL could be ranked

1. Add NULLS LAST inside the OVER clause when the ranked column can be NULL.