How Do You Find the Nth Highest Value?

Two answers, depending on whether you mean the third highest distinct value or the third row.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  salary_cents INTEGER NOT NULL
);

INSERT INTO employees (id, name, salary_cents) VALUES
  (1, 'Grace Hopper',   150000),
  (2, 'Alan Turing',    150000),
  (3, 'Ada Lovelace',   120000),
  (4, 'Joan Clarke',     90000),
  (5, 'Claude Shannon',  80000);

-- The third highest ROW.
SELECT name, salary_cents
FROM employees
ORDER BY salary_cents DESC
LIMIT 1 OFFSET 2;

-- The third highest DISTINCT value: Claude Shannon, at 80000.
SELECT name, salary_cents
FROM employees
WHERE salary_cents = (
  SELECT DISTINCT salary_cents
  FROM employees
  ORDER BY salary_cents DESC
  LIMIT 1 OFFSET 2
)
ORDER BY id;

The first query returns Ada Lovelace at 120000 — the third row when sorted, counting Grace and Alan separately even though they tie. The second returns Claude Shannon at 80000, because the distinct salaries are 150000, 120000, 90000 and 80000, and the third of those is 90000... except that the OFFSET lands on the third distinct value, which is 90000, held by Joan Clarke.

Let the query settle it: the second query returns Joan Clarke at 90000. The distinct salaries in descending order are 150000, 120000, 90000 and 80000, so the third distinct value is 90000.

The general rule that follows from this is worth having ready for an interview. To find the Nth highest distinct value, wrap the DISTINCT subquery in a comparison. To find the Nth row, use LIMIT and OFFSET directly on the sorted table. To find every row holding the Nth highest distinct value, use DENSE_RANK and filter on it — that is the version that handles ties correctly and is usually what a business question wants.

Four, and each trades simplicity against correctness around ties.

LIMIT and OFFSET. ORDER BY value DESC LIMIT 1 OFFSET n-1. The simplest to write and the fastest to execute, because it stops after reading n rows. Its weakness is ties: it counts rows, not distinct values, so a tie at the top consumes two of your N positions and the result is not the Nth highest distinct value. It is also non-deterministic when the sort key ties, because the engine chooses which of the tied rows comes first.

DENSE_RANK with a filter. WHERE DENSE_RANK() OVER (ORDER BY value DESC) = n. This counts distinct values, so ties share a position and the numbering does not skip. It returns every row holding the Nth highest value. The cost is that it has to scan and rank the whole set, and it needs a subquery because you cannot filter on a window function in the same query.

ROW_NUMBER with a filter. WHERE ROW_NUMBER() OVER (ORDER BY value DESC) = n. Counts rows exactly like OFFSET, but it lets you control the tiebreaker inside the window, which fixes the non-determinism. It is the right choice when the business rule says "the Nth row" and you want that answer to be reproducible.

The correlated MAX chain. Nesting a MAX inside a MAX inside a comparison. It finds the Nth highest distinct value without any window-function support, so it works on the oldest engines. The cost is that each additional level of nesting makes it harder to read, and it is slow if you try to generalise it to a large N.

For an interview, the useful thing is to say which one you would use and why. My default for "the Nth highest value" would be DENSE_RANK, because it matches the business meaning and handles ties. For "the Nth row" I would use ROW_NUMBER with an explicit tiebreaker. And I would say out loud that OFFSET is the version that looks obvious and is usually wrong.

A window function ranks within each group, and the outer query keeps the rows at the second position.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 1,  90.00),
  (4, 2, 610.00),
  (5, 2,  75.25),
  (6, 2, 250.00);

WITH ranked AS (
  SELECT id, customer_id, amount,
         DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS amount_rank
  FROM orders
)
SELECT id, customer_id, amount
FROM ranked
WHERE amount_rank = 2
ORDER BY customer_id, id;

Two rows come back: order 1 for customer 1 at 120.00 and order 6 for customer 2 at 250.00. Customer 1's largest order is 340.50, so the second largest is the 120.00 one; customer 2's largest is 610.00, so the second largest is the 250.00 one.

Two details. DENSE_RANK rather than RANK, because RANK skips a position after a tie and would then find the wrong row. And the ORDER BY inside the window is the descending sort that makes rank 1 the highest amount — the ranks count from the top.

Note that customer 1's amounts are 340.50, 120.00 and 90.00, so the second highest is 120.00 and the order id that carries it is 1. The result shows order 2, which carries 340.50 — because order 2 has the largest amount, and DENSE_RANK 1 is the largest. The second highest is order 1 at 120.00.

Let the query settle it: the two rows returned are order 1 for customer 1 at 120.00, and order 6 for customer 2 at 250.00.