Write a query that finds the third highest value in a column.
You number the values and keep the one at the position you want.
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, 'Ada', 900000),
(2, 'Grace', 700000),
(3, 'Alan', 500000),
(4, 'Katherine', 300000);
-- The third highest salary.
SELECT salary_cents
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY salary_cents DESC) AS position, salary_cents
FROM employees
) AS ranked
WHERE position = 3;
salary_cents
500000
So what is happening? The rows are numbered from the biggest downwards, and the row with the number three is the third biggest.
There is one detail to notice. The function ROW_NUMBER counts the rows, so if two people have the same salary the position of the second one depends on the order of the rows. To make the answer steady, add the identifier to the order inside the window.
ROW_NUMBER() OVER (ORDER BY salary_cents DESC, id) AS position
The next question in this topic compares the ways of doing it, and the last one does it inside each group.