Nth highest and ranking

"Find the second highest salary" is the most asked SQL question in tester interviews. It looks easy. Most candidates lose points on the follow-up questions: what about ties (two people with the same salary), what about the Nth (any position, such as 2nd, 3rd or 4th), can you do it without LIMIT, and what if each department needs its own answer. This article answers all of them on the company data, where two employees share 95,000 and two share 64,000. Ties like these are what the interviewer is testing.

Second highest: the subquery answer

The highest salary below the top salary is the second highest. The inner query (a query inside another query, called a subquery) finds the top salary. This works in every SQL database and handles a tie at the top:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

second_highest
95000

If there is no second value (one employee, or everyone earns the same), the query returns NULL. Say this; interviewers often ask.

Nth highest with LIMIT and OFFSET

Sort the distinct salaries from high to low, skip N - 1 of them, and take one. For N = 3:

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

salary
82000

Without DISTINCT, the two people at 95,000 take two places, so OFFSET 2 lands on 95,000 again. That is exactly the mistake the follow-up checks. This form works in MySQL, PostgreSQL and SQLite; SQL Server writes OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY after ORDER BY.

Without LIMIT or window functions

Some interviewers forbid LIMIT. A salary is the Nth highest when exactly N - 1 distinct salaries are higher than it. A correlated count says that directly. "Correlated" means the inner query uses a column of the outer row, here e.salary (see Subqueries). For N = 4:

SELECT DISTINCT salary FROM employees e
WHERE 3 = (SELECT COUNT(DISTINCT x.salary) FROM employees x WHERE x.salary > e.salary);

salary
70000

With a window function

A window function computes a value for each row from a set of related rows, without merging the rows the way GROUP BY does. The part after OVER says which rows to look at and in which order. Window functions exist in MySQL 8.0 and later, PostgreSQL and SQLite. Three ranking functions differ only on ties:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees
WHERE salary >= 70000
ORDER BY salary DESC, name;

name | salary | row_num | rnk | dense
Asha Rao | 150000 | 1 | 1 | 1
Dev Malhotra | 95000 | 3 | 2 | 2
Vikram Shah | 95000 | 2 | 2 | 2
Neha Gupta | 82000 | 4 | 4 | 3
Arjun Das | 70000 | 5 | 5 | 4

ROW_NUMBER never repeats a number, and which tied row gets which number is not defined unless you add a tie-breaker. RANK repeats on ties and then skips (1, 2, 2, 4). DENSE_RANK repeats and does not skip (1, 2, 2, 3). For "Nth highest distinct salary" use DENSE_RANK; it also shows who earns it. A window function cannot go in WHERE, because it is computed after WHERE, so filter in an outer query:

SELECT name, salary FROM (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

name | salary
Vikram Shah | 95000
Dev Malhotra | 95000

Per group: PARTITION BY

PARTITION BY restarts the numbering for each group. "The two highest-paid employees in each department" needs ROW_NUMBER plus a tie-breaker, so the answer has at most two rows per department and is the same on every run:

SELECT dept_id, name, salary FROM (
  SELECT dept_id, name, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, id) AS rn
  FROM employees
  WHERE dept_id IS NOT NULL
) ranked
WHERE rn <= 2
ORDER BY dept_id, rn;

dept_id | name | salary
1 | Asha Rao | 150000
1 | Vikram Shah | 95000
2 | Neha Gupta | 82000
2 | Rohan Mehta | 64000
3 | Arjun Das | 70000
3 | Kavya Iyer | 52000
4 | Imran Khan | 58000

If the question instead says "everyone whose salary is among the top two salaries of the department", use DENSE_RANK and accept more than two rows when there are ties. Ask which one is meant.

Running totals

With ORDER BY inside OVER, SUM becomes a running total: each row adds its amount to the rows before it.

SELECT id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date, id) AS running_total
FROM orders
WHERE status = 'DELIVERED'
ORDER BY order_date, id;

id | order_date | amount | running_total
101 | 2026-01-05 | 12000 | 12000
103 | 2026-02-11 | 8000 | 20000
106 | 2026-03-15 | 9500 | 29500
107 | 2026-03-18 | 7000 | 36500
110 | 2026-04-10 | 2500 | 39000

Include a unique column (here id) in the window's ORDER BY. If two rows share a date and the window orders by date alone, the database treats them as equals and gives both the same total. Orders 104 and 109 share a date:

SELECT id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
WHERE status IN ('SHIPPED', 'DELIVERED') AND order_date < '2026-03-01'
ORDER BY order_date, id;

id | order_date | amount | running_total
101 | 2026-01-05 | 12000 | 12000
103 | 2026-02-11 | 8000 | 20000
104 | 2026-02-20 | 15000 | 50000
109 | 2026-02-20 | 15000 | 50000

How to answer in an interview

Write the MAX-below-MAX answer first, because it works everywhere. Then offer the general form with DENSE_RANK and say why it is DENSE_RANK: ties must not use up a position. State what happens on ties and when there is no answer. For "top N per group", ask whether ties should all be returned before choosing ROW_NUMBER or DENSE_RANK.

Common mistakes

• Forgetting DISTINCT before LIMIT and OFFSET.
• Using RANK for "Nth highest", which skips numbers after a tie.
• Filtering on a window function in WHERE.
• ROW_NUMBER without a tie-breaker, so the chosen rows can change between runs.

Recap

Second highest is MAX below MAX. For the Nth highest, use DISTINCT with LIMIT and OFFSET, a correlated count, or DENSE_RANK. Top N per group is ROW_NUMBER with PARTITION BY and a tie-breaker, filtered in an outer query. Always say how ties are handled.