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.
| Table | employees, 7 rows with ties |
|---|---|
| Dialects | MySQL, PostgreSQL, SQL Server, Oracle |
| Level | Easy 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.
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
- Do you mean the Nth highest salary value (distinct) or the Nth row? Ask the interviewer.
- What should be returned when fewer than N distinct salaries exist: an empty set or NULL?
- Which portable construct to use: DENSE_RANK works on MySQL 8+, PostgreSQL, SQL Server and Oracle; LIMIT OFFSET works on MySQL and PostgreSQL only.
Nth Highest Salary in SQL: questions with worked answers
1 Find the second highest salary using MAX and a subquery.
easyTake 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.
sqlSELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); -- second_highest -- -------------- -- 750002 Find the second highest salary with LIMIT and OFFSET (MySQL, PostgreSQL).
easyDISTINCT 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.
sqlSELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; -- salary -- ------ -- 75000sqlSELECT (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 -- -------------- -- 750003 Find the Nth highest salary with DENSE_RANK. Why not RANK or ROW_NUMBER?
mediumDENSE_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.
sqlSELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees) t WHERE dr = 3; -- salary -- ------ -- 600004 Show the difference between RANK, DENSE_RANK and ROW_NUMBER on this data.
mediumThe 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".
sqlSELECT 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 | 75 Find the Nth highest salary without LIMIT or window functions (correlated subquery).
mediumA 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.
sqlSELECT DISTINCT e1.salary FROM employees e1 WHERE 2 = (SELECT COUNT(DISTINCT e2.salary) FROM employees e2 WHERE e2.salary > e1.salary); -- salary -- ------ -- 600006 Find the Nth highest salary using TOP (SQL Server) and ROWNUM or FETCH (Oracle).
mediumSQL 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 Write a MySQL function that returns the Nth highest salary, returning NULL if it does not exist.
mediumA 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.
sqlDELIMITER // 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); -- NULL8 Find the second highest salary in each department.
mediumRank 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.
sqlSELECT 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 | 750009 List the employees who earn the second highest salary, not just the value.
mediumUse 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".
sqlSELECT 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 | 7500010 How do NULL salaries affect these queries?
mediumIn 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 Find the top 2 distinct salaries and the employees on them.
hardRank 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".
sqlSELECT 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 | 7500012 Which solution is fastest, and what indexing helps?
hardWith 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 What is the median salary, and how would you compute it?
mediumThe 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.
sqlSELECT 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
- PL/SQL Interview Questions and Answers
- PostgreSQL Interview Questions and Answers
- Python Coding Interview Questions with Solutions
- Python Decorators Interview Questions
- Python Exception Handling Interview Questions
- Python File Handling Interview Questions
- Python Generators and Iterators Interview Questions
- Python Interview Questions and Answers
- Python Lambda, Map, Filter and Reduce Interview Questions
- Python List, Dict and Set Interview Questions
- Python Memory Management Interview Questions
- Python Multithreading and Multiprocessing Interview Questions