Autocoder

Nth Highest Salary in SQL: Interview Questions

Last updated 2026-11-19

"Find the second highest salary" is probably the most repeated SQL interview question, and the follow-ups are what actually test you: what if two people tie, what if there is no second salary, what about the Nth, what about per department, and what is the fastest way. This page walks through every standard solution on one small table so you can see exactly how each handles ties and missing values.

The table has seven employees with salaries 90000, 75000 (twice), 60000 (twice), 50000 and 40000, so there are five distinct salaries and the second highest is 75000. All results below were produced by running the queries. Syntax notes cover MySQL, PostgreSQL, SQL Server and Oracle. See also SQL window functions and how to crack a SQL interview.

Tableemployees, 7 rows with ties
DialectsMySQL, PostgreSQL, SQL Server, Oracle
LevelEasy to hard

Sample data

Distinct salaries in descending order are 90000, 75000, 60000, 50000, 40000. Gita (40000) has no department, and Asha (90000) is the top earner.

sql
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT, manager_id INT);
INSERT INTO departments VALUES (10,'Sales'),(20,'Engineering'),(30,'HR'),(40,'Legal');
INSERT INTO employees VALUES
(1,'Asha',20,90000,NULL),(2,'Bilal',20,75000,1),(3,'Chitra',10,60000,1),
(4,'Dev',10,60000,3),(5,'Esha',30,50000,1),(6,'Farid',20,75000,1),(7,'Gita',NULL,40000,5);

The three decisions in every answer

Nth Highest Salary in SQL: questions with worked answers

  1. 1

    Find the second highest salary using MAX and a subquery.

    easy

    Take the maximum of all salaries strictly below the overall maximum. It handles ties because it compares values, not rows, and it returns NULL (one row, NULL value) if there is no second salary. This is the only solution that works on any database without window functions or LIMIT.

    sql
    SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
    -- second_highest
    -- --------------
    -- 75000
  2. 2

    Find the second highest salary with LIMIT and OFFSET (MySQL, PostgreSQL).

    easy

    DISTINCT first, then sort descending, skip one and take one. Without DISTINCT the offset skips a row, not a value, so with two people on 90000 you would return 90000 again. If no row exists the result is empty, not NULL. Wrap it in a subquery to turn it into NULL.

    sql
    SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
    -- salary
    -- ------
    -- 75000
    sql
    SELECT (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest;
    -- The scalar subquery form returns NULL when the row does not exist
    -- second_highest
    -- --------------
    -- 75000
  3. 3

    Find the Nth highest salary with DENSE_RANK. Why not RANK or ROW_NUMBER?

    medium

    DENSE_RANK numbers distinct salary values without gaps, so rank N is the Nth highest value however many people share it. RANK leaves gaps after ties, so rank 3 might not exist (after two tied at rank 2 comes rank 4). ROW_NUMBER numbers rows, so it returns the Nth row, ignoring ties. This is the portable answer for MySQL 8, PostgreSQL, SQL Server and Oracle. The third highest is 60000, and both people on it are returned if you drop DISTINCT.

    sql
    SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) t WHERE dr = 3;
    -- salary
    -- ------
    -- 60000
  4. 4

    Show the difference between RANK, DENSE_RANK and ROW_NUMBER on this data.

    medium

    The ties at 75000 and 60000 show it. RANK skips (1, 2, 2, 4, 4, 6, 7), DENSE_RANK does not (1, 2, 2, 3, 3, 4, 5), and ROW_NUMBER is 1 to 7. Only DENSE_RANK equals "Nth distinct value".

    sql
    SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense, ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS rn FROM employees ORDER BY salary DESC, emp_id;
    -- name   | salary | rnk | dense | rn
    -- -------+--------+-----+-------+---
    -- Asha   | 90000  | 1   | 1     | 1
    -- Bilal  | 75000  | 2   | 2     | 2
    -- Farid  | 75000  | 2   | 2     | 3
    -- Chitra | 60000  | 4   | 3     | 4
    -- Dev    | 60000  | 4   | 3     | 5
    -- Esha   | 50000  | 6   | 4     | 6
    -- Gita   | 40000  | 7   | 5     | 7
  5. 5

    Find the Nth highest salary without LIMIT or window functions (correlated subquery).

    medium

    A salary is the Nth highest if exactly N-1 distinct salaries are greater than it. The correlated subquery counts distinct greater salaries per candidate row. This works on any database but is O(n squared) without an index on salary, so mention it as the old-school answer rather than the fastest. For N = 3, the salary with exactly 2 larger distinct values is 60000.

    sql
    SELECT DISTINCT e1.salary FROM employees e1 WHERE 2 = (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e2.salary > e1.salary);
    -- salary
    -- ------
    -- 60000
  6. 6

    Find the Nth highest salary using TOP (SQL Server) and ROWNUM or FETCH (Oracle).

    medium

    SQL Server: nest SELECT TOP 1 salary FROM (SELECT DISTINCT TOP N salary FROM employees ORDER BY salary DESC) t ORDER BY salary ASC. Oracle 12c and later: SELECT DISTINCT salary FROM employees ORDER BY salary DESC OFFSET N-1 ROWS FETCH NEXT 1 ROWS ONLY (also valid in PostgreSQL and SQL Server 2012+). Older Oracle needs a ROWNUM wrapper, because ROWNUM is assigned before ORDER BY unless applied in an outer query. DENSE_RANK avoids all dialect differences.

  7. 7

    Write a MySQL function that returns the Nth highest salary, returning NULL if it does not exist.

    medium

    A common platform-style question. Compute the offset into a local variable first, because MySQL accepts variables and parameters in LIMIT inside stored programs but not expressions such as N-1, and return NULL via a scalar subquery. The DELIMITER lines belong to the mysql client only.

    sql
    DELIMITER //
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    READS SQL DATA
    BEGIN
      DECLARE off INT;
      SET off = N - 1;
      RETURN (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET off);
    END //
    DELIMITER ;
    -- SELECT getNthHighestSalary(2);  -- 75000
    -- SELECT getNthHighestSalary(9);  -- NULL
  8. 8

    Find the second highest salary in each department.

    medium

    Rank salaries within each department with DENSE_RANK and PARTITION BY, then keep rank 2. A department with only one distinct salary, or the Sales pair tied at 60000, has no second value and does not appear. Engineering has 90000 and 75000, so its second is 75000.

    sql
    SELECT dept_id, salary AS second_highest FROM (
      SELECT dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dr
      FROM employees WHERE dept_id IS NOT NULL
    ) t WHERE dr = 2 GROUP BY dept_id, salary ORDER BY dept_id;
    -- dept_id | second_highest
    -- --------+---------------
    -- 20      | 75000
  9. 9

    List the employees who earn the second highest salary, not just the value.

    medium

    Use the value as a filter: select the employees whose salary equals the second-highest value. Both Bilal and Farid earn 75000, so both are returned, which is exactly why a LIMIT 1 answer is wrong when the question says "employees".

    sql
    SELECT name, salary FROM employees WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees)) ORDER BY emp_id;
    -- name  | salary
    -- ------+-------
    -- Bilal | 75000
    -- Farid | 75000
  10. 10

    How do NULL salaries affect these queries?

    medium

    In ORDER BY ... DESC, MySQL and SQL Server place NULLs last, while PostgreSQL and Oracle place them first, so DISTINCT salary ORDER BY salary DESC LIMIT 1 returns NULL as the highest in PostgreSQL. MAX, DENSE_RANK with ties and comparisons all ignore or misplace NULL. Filter WHERE salary IS NOT NULL before ranking, or use NULLS LAST in PostgreSQL and Oracle.

  11. 11

    Find the top 2 distinct salaries and the employees on them.

    hard

    Rank with DENSE_RANK and keep ranks up to 2. This returns every employee tied at those salary levels, so the row count is not 2. It is the general form of "top N including ties".

    sql
    SELECT name, salary FROM (SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) t WHERE dr <= 2 ORDER BY salary DESC, name;
    -- name  | salary
    -- ------+-------
    -- Asha  | 90000
    -- Bilal | 75000
    -- Farid | 75000
  12. 12

    Which solution is fastest, and what indexing helps?

    hard

    With an index on salary, ORDER BY salary DESC LIMIT 1 OFFSET N-1 can walk the index in order and stop after N entries, which is the fastest for small N (though DISTINCT with duplicates may scan more). MAX with a subquery is two index lookups for the second highest. DENSE_RANK scans and sorts the whole table, which is fine for interview sizes but heavier on millions of rows. The correlated subquery is quadratic. Read the plan with EXPLAIN. More in SQL indexes questions.

  13. 13

    What is the median salary, and how would you compute it?

    medium

    The middle value of the sorted salaries, or the average of the two middle values when the count is even. PostgreSQL has PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary). MySQL has no median function, so number the rows and take the middle one or two, as below. With 7 salaries the median is the 4th value in order, 60000.

    sql
    SELECT AVG(salary) AS median FROM (SELECT salary, ROW_NUMBER() OVER (ORDER BY salary) AS rn, COUNT(*) OVER () AS cnt FROM employees) t WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2);
    -- median
    -- ------
    -- 60000

Frequently asked questions

What is the safest answer in an interview?

DENSE_RANK in a subquery, with DISTINCT, because it handles ties and works on every modern database. Then mention the LIMIT OFFSET and MAX variants.

Why does LIMIT 1 OFFSET 1 sometimes return the highest salary?

If you forget DISTINCT and the top salary is shared, the offset skips only one of the tied rows.

How do I return NULL instead of no rows?

Wrap the query in a scalar subquery, SELECT (query) AS col, or use MAX over the filtered set.

Is this question asked for freshers too?

Yes, very often. See SQL for freshers for the rest of the usual list.

Related guides

More in Programming Language Interview Questions by Language

See all in this section