How to find the second-highest value per group in SQL

"Find the second-highest X per Y" is one of the most recycled SQL interview questions there is — second-highest salary per department, runner-up price per category, second-most-recent order per customer. It looks like it needs a special function. It doesn't; there is no SECOND_MAX. It needs a ranking function and a place to filter on the rank.

The naive attempts, and why they fail

The instinct most people reach for first is nested aggregation:

SELECT category, MAX(price) AS second_highest
FROM products
WHERE price < (SELECT MAX(price) FROM products p2 WHERE p2.category = products.category)
GROUP BY category;

This works, but it's a dead end the moment the question changes to "third-highest" — you'd need another nested MAX, then another. It also silently breaks if two products share the highest price: both get excluded by the <, and the "second highest" the query returns is actually the third distinct value.

The pattern: rank, then filter

Number every row within its group, ordered by the value you care about, then keep the row numbered 2:

SELECT category, price,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
FROM products;

PARTITION BY category restarts the numbering for every category independently. ORDER BY price DESC makes rank 1 the most expensive item in that category, rank 2 the runner-up. This generalizes for free — third-highest is rn = 3, no new logic.

The trap: WHERE can't see the window function

The next instinct is to filter in the same query:

SELECT category, price
FROM products
WHERE ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) = 2;

Postgres rejects this outright. WHERE is evaluated before window functions are computed, in the query's real execution order (FROM → WHERE → GROUP BY → window functions → SELECT → ORDER BY), so at the point WHERE runs, rn doesn't exist yet as a column to compare against. The fix is to finish the window pass in a subquery or CTE, then filter the outer query on the now-ordinary rn column:

WITH ranked AS (
  SELECT category, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT category, price AS second_highest
FROM ranked
WHERE rn = 2;

More on why in Query execution order.

Which ranking function, and it matters

ROW_NUMBER is only correct when you can assume no ties. Once two products in a category share the highest price, the three ranking functions diverge:

If two products are tied for most expensive, ROW_NUMBER picks one of them arbitrarily to be "rank 1" and silently drops the other from the result entirely — a real bug if the interviewer's data has ties and you didn't ask. Saying this out loud ("I'm assuming no ties here — should I use RANK instead if there are?") is usually worth more than the query itself.

More in Window functions and Subqueries & CTEs.