How Do You Find the Second Highest Salary?

The subtlety is what "second highest" means when two people earn the same top salary. The second highest distinct value is the largest salary below the maximum. The second highest row is simply the next row when sorted — which is a different number whenever there is a tie.

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);

-- The second highest DISTINCT salary: Ada, at 120000.
SELECT name, salary_cents
FROM employees
WHERE salary_cents = (
  SELECT MAX(salary_cents)
  FROM employees
  WHERE salary_cents < (SELECT MAX(salary_cents) FROM employees)
)
ORDER BY id;

-- The second highest ROW: Alan, at 150000.
SELECT name, salary_cents
FROM employees
ORDER BY salary_cents DESC
LIMIT 1 OFFSET 1;

The two queries return different people. The first returns Ada Lovelace at 120000, because 120000 is the largest salary strictly below the maximum of 150000. The second returns Alan Turing at 150000, because he is the second row once you sort by salary descending — his salary ties with Grace's, and the tie is broken arbitrarily.

So which one does the interviewer want? The phrase "second highest salary" almost always means the second highest distinct value, and the follow-up question is usually whether you notice the tie. Saying so out loud, and showing both versions, is the strongest possible answer.

The first query's shape is worth explaining. The inner MAX finds the top salary. The second MAX, over rows strictly below it, finds the next distinct value down. The outer WHERE then matches every employee earning that amount, so it correctly returns both employees if they tie at the second value.

Two shorter alternatives. A window function — DENSE_RANK() OVER (ORDER BY salary_cents DESC) = 2 — is the clearest of them, and DENSE_RANK is the right function precisely because it does not skip ranks after a tie. And if the interviewer says "the second person, counting ties separately", then ROW_NUMBER is what you want, with a deterministic tiebreaker.

Use a self-comparison: find the maximum salary, then find the largest salary strictly below it, then match the rows.

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);

SELECT name, salary_cents
FROM employees
WHERE salary_cents = (
  SELECT MAX(salary_cents)
  FROM employees
  WHERE salary_cents < (SELECT MAX(salary_cents) FROM employees)
)
ORDER BY id;

One row comes back: Ada Lovelace at 120000.

The three-level structure is the point. The innermost query finds the top salary, 150000. The middle query looks only at employees earning strictly less than that and takes their maximum, which is 120000. The outer query then finds everyone earning exactly 120000 — which is one person here, but would be two if there were a tie at that level.

That last property is the difference between this and the LIMIT and OFFSET version, and it is what makes this the safer answer. With OFFSET, a tie at the top pushes the second-highest distinct salary out of the result entirely.