#Ranking and Nth highest
Added"Find the second highest salary" is probably the most-asked SQL question there is, and the reason is that it looks trivial but hides three decisions: what to do with ties, what to return when there is no answer, and which tool to reach for (an aggregate, LIMIT/OFFSET, or a window function). An interviewer learns more from how you handle the 142,000 that appears twice than from the query itself.
A good mental model is a podium with shared steps. DENSE_RANK puts everyone with the same score on the same step and the next score on the very next step (1, 2, 2, 3). RANK does the same but leaves a gap after a shared step, like Olympic medals (1, 2, 2, 4). ROW_NUMBER refuses to share and breaks ties arbitrarily (1, 2, 3, 4). "Nth highest salary" almost always means the Nth distinct value — the DENSE_RANK step — and saying that out loud before writing anything is half the answer.
Every problem in this chapter uses the site's sample schema (see The sample schema used on this site) or a small purpose-built table shown with the question. The salaries you'll see most are these ten:
| Employee | Salary | Employee | Salary | |
|---|---|---|---|---|
| Asha | 185,000 | Grace | 99,000 | |
| Ben | 142,000 | Farid | 95,000 | |
| Chloe | 142,000 | Jonas | 91,000 | |
| Elena | 130,000 | Hiro | 88,000 | |
| Dev | 118,000 | Isla | 72,000 |
Write a query that returns the second highest salary from employees as a single column called second_highest. If there is no second highest salary (for example in a department with fewer than two distinct salaries), it should return NULL, not an empty result.
185000.00 142000.00 142000.00 130000.00 118000.00
99000.00 95000.00 91000.00 88000.00 72000.00Show answer
The answer is 142,000. The simplest query that's correct for ties and returns NULL when there's no answer is "the highest salary that's lower than the highest salary":
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);+----------------+
| second_highest |
+----------------+
| 142000.00 |
+----------------+- The subquery
(SELECT MAX(salary) FROM employees)runs once and returns185000.00— it's not correlated, so MySQL evaluates it a single time. - The
WHEREkeeps every row withsalary < 185000: nine rows, from Ben's142000down to Isla's72000. Asha is the only row removed. MAX(salary)over those nine rows is142000.00. Ben and Chloe both have it, butMAXreturns a value, not a row, so the tie can't produce two results.- The
NULLrequirement comes for free: an aggregate withoutGROUP BYalways returns exactly one row. If theWHEREmatched nothing,MAXover an empty set isNULL. - The limitation is that it only generalises to "third highest" by nesting another level, which gets ugly fast — that's where the next two forms win.
Alternative 1: DISTINCT + LIMIT/OFFSET, wrapped so it returns NULL. Skip one distinct salary and take the next. On its own this returns no row when there's no second salary, so the problem's trick is to wrap it in a scalar subquery. Here it's run for Research (department 4), which has no employees, to show the difference:
SELECT DISTINCT salary FROM employees
WHERE department_id = 4
ORDER BY salary DESC LIMIT 1 OFFSET 1;
SELECT (SELECT DISTINCT salary FROM employees
WHERE department_id = 4
ORDER BY salary DESC LIMIT 1 OFFSET 1) AS second_highest;Empty set
+----------------+
| second_highest |
+----------------+
| NULL |
+----------------+SELECT DISTINCT salary … ORDER BY salary DESCproduces the distinct salaries highest first. For the whole table that's185000, 142000, 130000, …— the duplicate142000collapses to one.LIMIT 1 OFFSET 1skips one row and returns the next, so for the whole table it returns142000.00.- For Research there are no rows at all, so the bare query returns an empty set — zero rows, which an application or a test harness sees as "no answer", not as
NULL. - Wrapping it as a scalar subquery in an outer
SELECTwith noFROMchanges that: the outer query always produces one row, and a scalar subquery that finds no row evaluates toNULL. - Without
DISTINCTthis form breaks on ties — see the next question.
Alternative 2: DENSE_RANK (MySQL 8.0+). Number the distinct salaries and pick rank 2:
WITH ranked AS (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT DISTINCT salary AS second_highest FROM ranked WHERE rnk = 2;+----------------+
| second_highest |
+----------------+
| 142000.00 |
+----------------+- The window function gives every row a rank: Asha
1, Ben2, Chloe2, Elena3… Ties share a rank andDENSE_RANKnever skips a number. WHERE rnk = 2keeps Ben's and Chloe's rows — two rows with the same salary.DISTINCTcollapses them to one value. Without it you'd return142000.00twice.- The filter has to sit in an outer query (a CTE or derived table): window functions are computed after
WHERE, soWHERE DENSE_RANK() OVER (…) = 2isn't allowed in the sameSELECT. - Like the
LIMITform, this returns an empty set when there's no rank 2, so wrap it the same way ifNULLis required.
| Approach | Ties | Generalises to N | "No answer" | Needs |
|---|---|---|---|---|
MAX(…) WHERE salary < MAX | Correct | Only by nesting | NULL automatically | Any version |
DISTINCT … LIMIT 1 OFFSET n-1 | Correct only with DISTINCT | Yes (literal offset) | Empty set unless wrapped | Any version |
DENSE_RANK() | Correct | Yes, and per group with PARTITION BY | Empty set unless wrapped | MySQL 8.0+ |
Learn more: ROW_NUMBER, RANK and DENSE_RANK and Subqueries.
A colleague wrote this to find the third highest salary. What does it return, and what's wrong with it?
SELECT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;Show answer
It returns 142,000, which is the second highest salary. The third highest distinct salary is 130,000. The bug is the missing DISTINCT: OFFSET counts rows, and the tie between Ben and Chloe uses up two of them.
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;+-----------+
| salary |
+-----------+
| 142000.00 |
+-----------+
+-----------+
| salary |
+-----------+
| 130000.00 |
+-----------+- Sorted highest first, the rows are
185000, 142000, 142000, 130000, ….OFFSET 2skips the first two rows — Asha's and one of the two 142,000 rows. - The next row is the other 142,000, so the buggy query returns
142000.00. - With
DISTINCT, the sorted list becomes185000, 142000, 130000, …— duplicates are removed beforeORDER BYandLIMITare applied (logical order:SELECT DISTINCT, thenORDER BY, thenLIMIT). - Skipping two values now lands on
130000.00, the correct third highest. - The bug is invisible on test data without ties, which is exactly why interviewers put a tie in the data. Asking "do ties count once?" before you write the query shows you've seen it.
Learn more: LIMIT and OFFSET and DISTINCT.
Write a stored function nth_highest_salary(n INT) that returns the Nth highest distinct salary, or NULL if there isn't one. This first attempt doesn't even compile — what's the error, and how do you fix it?
CREATE FUNCTION nth_highest_salary(n INT) RETURNS DECIMAL(10,2)
READS SQL DATA
RETURN (SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT n - 1, 1);Show answer
MySQL's LIMIT only accepts integer literals, ? placeholders, or stored-program variables and parameters — not expressions. n - 1 is an expression, so it's a syntax error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '- 1, 1)' at line 3The fix is to compute the offset into a local variable first, then use the variable in LIMIT:
DELIMITER //
CREATE FUNCTION nth_highest_salary(n INT) RETURNS DECIMAL(10,2)
READS SQL DATA
BEGIN
DECLARE skip INT DEFAULT n - 1;
RETURN (SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT skip, 1);
END//
DELIMITER ;
SELECT nth_highest_salary(1) AS n1, nth_highest_salary(2) AS n2,
nth_highest_salary(3) AS n3, nth_highest_salary(9) AS n9;+-----------+-----------+-----------+----------+
| n1 | n2 | n3 | n9 |
+-----------+-----------+-----------+----------+
| 185000.00 | 142000.00 | 130000.00 | 72000.00 |
+-----------+-----------+-----------+----------+DELIMITER //tells the mysql client not to end the statement at the first;inside the function body. It's a client feature, not SQL; GUI tools and drivers don't need it.DECLARE skip INT DEFAULT n - 1evaluates the expression once and stores it.LIMIT skip, 1is legal becauseskipis a local variable, which MySQL allows inLIMITinside stored programs (since 5.5).LIMIT skip, 1is the two-argument form: offset first, then row count. Forn = 3, it skips two distinct salaries (185000,142000) and returns130000.00.- There are only nine distinct salaries (ten employees, one tie), so
n9is the lowest,72000.00, andnth_highest_salary(10)would find no row — theRETURN (subquery)then returnsNULL, which is what the spec asks for. READS SQL DATAdeclares that the function reads but doesn't modify data. Leave it out and, with binary logging on (the default since 8.0),CREATE FUNCTIONfails withERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled.
Alternative 1: a prepared statement. Outside a stored program, LIMIT ? takes a placeholder, so application code (or a session) can bind the offset:
SET @skip = 2;
PREPARE stmt FROM 'SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT ?, 1';
EXECUTE stmt USING @skip;
DEALLOCATE PREPARE stmt;+-----------+
| salary |
+-----------+
| 130000.00 |
+-----------+PREPAREparses the statement once with?standing in for the offset. It's the same mechanism drivers like mysql2 use forexecute().EXECUTE … USING @skipbinds2, so the query skips two distinct salaries and returns130000.00.- This is how you'd do it from Node: compute
n - 1in JavaScript and pass it as a parameter — never by concatenating it into the SQL string.
Alternative 2: no LIMIT, no window functions. The pre-8.0 classic: a salary is the Nth highest if exactly N - 1 distinct salaries are higher than it.
SELECT DISTINCT e1.salary
FROM employees e1
WHERE 3 - 1 = (SELECT COUNT(DISTINCT e2.salary)
FROM employees e2
WHERE e2.salary > e1.salary);+-----------+
| salary |
+-----------+
| 130000.00 |
+-----------+- For each row
e1, the correlated subquery counts the distinct salaries strictly greater thane1.salary. - For Elena (
130000) the higher distinct salaries are185000and142000, so the count is2, which equals3 - 1. She's kept. - For Ben and Chloe (
142000) the count is1; for Dev (118000) it's3. Neither matches. DISTINCTin the outer query matters when several people share the Nth salary — try it with2 - 1and you'd get Ben and Chloe's142000twice without it.- The cost: the subquery runs once per row, so it's O(n²) without an index on
salary. It's the answer to "how would you do it without window functions?", not the one to ship.
The window-function version for any N is SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) AS r WHERE rnk = 3, which also returned 130000.00.
Learn more: Stored functions and SQL injection and prepared statements.
Predict the three ranking columns for the top six salaries.
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
ORDER BY salary DESC, name
LIMIT 6;Show answer
Ben and Chloe tie on 142,000. ROW_NUMBER gives them 2 and 3, RANK gives both 2 and then jumps to 4, DENSE_RANK gives both 2 and continues with 3.
+-------+-----------+---------+-----+-------+
| name | salary | row_num | rnk | dense |
+-------+-----------+---------+-----+-------+
| Asha | 185000.00 | 1 | 1 | 1 |
| Ben | 142000.00 | 2 | 2 | 2 |
| Chloe | 142000.00 | 3 | 2 | 2 |
| Elena | 130000.00 | 4 | 4 | 3 |
| Dev | 118000.00 | 5 | 5 | 4 |
| Grace | 99000.00 | 6 | 6 | 5 |
+-------+-----------+---------+-----+-------+- All three functions sort by
salary DESCinside the window. Asha is first everywhere:1, 1, 1. ROW_NUMBERnumbers rows1, 2, 3, …with no sharing. Ben and Chloe tie, so which of them gets 2 is not defined by the query — here Ben got 2, but nothing guarantees it. Add a tiebreaker (ORDER BY salary DESC, id) when you need a stable result.RANKis "1 + the number of rows strictly ahead of me". Ben and Chloe have one row ahead (Asha), so both are2. Elena has three rows ahead, so she's4— the gap is the "missing" 3.DENSE_RANKis "1 + the number of distinct values ahead of me". Elena has two distinct salaries ahead, so she's3. No gaps, ever.- The outer
ORDER BY salary DESC, nameonly controls display order; it doesn't change how the window functions numbered the rows.
Before MySQL 8.0 there were no window functions, and you'd compute ranks with correlated subqueries. Both give the same numbers as above:
SELECT e1.name, e1.salary,
(SELECT COUNT(DISTINCT e2.salary) FROM employees e2
WHERE e2.salary >= e1.salary) AS dense,
(SELECT COUNT(*) FROM employees e2
WHERE e2.salary > e1.salary) + 1 AS rnk
FROM employees e1
ORDER BY e1.salary DESC, e1.name
LIMIT 6;+-------+-----------+-------+------+
| name | salary | dense | rnk |
+-------+-----------+-------+------+
| Asha | 185000.00 | 1 | 1 |
| Ben | 142000.00 | 2 | 2 |
| Chloe | 142000.00 | 2 | 2 |
| Elena | 130000.00 | 3 | 4 |
| Dev | 118000.00 | 4 | 5 |
| Grace | 99000.00 | 5 | 6 |
+-------+-----------+-------+------+densecounts distinct salaries greater than or equal to mine. For Elena:185000, 142000, 130000→3.rnkcounts rows strictly greater than mine and adds one. For Elena: Asha, Ben, Chloe →3 + 1 = 4.- The definitions are exactly the ones in the walkthrough above, written out by hand — which is a good way to show you understand what the window functions compute.
- Each subquery runs once per outer row, so this is quadratic; with window functions MySQL sorts once and assigns ranks in a single pass.
NTILE(4) OVER (ORDER BY salary DESC) is the related "bucket" function: on this data it put Asha, Ben and Chloe in quartile 1 and Hiro and Isla in quartile 4, because ten rows don't split evenly and the earlier buckets get the extra rows.
Learn more: ROW_NUMBER, RANK and DENSE_RANK and Correlated subqueries.
MySQL has no MEDIAN() function. Find the median salary of all employees, then the median per department.
Show answer
The overall median is 108,500: with ten salaries, it's the average of the 5th and 6th (99,000 and 118,000). The trick is to number the rows, count them, and average the one or two middle rows:
WITH ordered AS (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS cnt
FROM employees
)
SELECT AVG(salary) AS median
FROM ordered
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2));+---------------+
| median |
+---------------+
| 108500.000000 |
+---------------+ROW_NUMBER() OVER (ORDER BY salary)numbers the salaries lowest first: Isla1(72,000) … Asha10(185,000).COUNT(*) OVER ()— an empty window — puts the total row count,10, on every row, so the outer query can use it without a second query.- With
cnt = 10,(cnt + 1) / 2is5.5.FLOORgives5andCEILgives6, so rows 5 and 6 are kept: Grace (99,000) and Dev (118,000). AVGof those two is108500. For an odd count, say 9,(9 + 1) / 2is exactly5,FLOORandCEILagree, andAVGof one row is just that row — so one formula covers both cases.- Compare the mean:
AVG(salary)over everyone is116200. Asha's 185,000 pulls the mean up but barely moves the median, which is why medians are used for salaries.
Per department is the same query with PARTITION BY on both window functions:
WITH ordered AS (
SELECT department_id, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary) AS rn,
COUNT(*) OVER (PARTITION BY department_id) AS cnt
FROM employees
WHERE department_id IS NOT NULL
)
SELECT department_id, AVG(salary) AS median, MIN(cnt) AS employees
FROM ordered
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY department_id;+---------------+---------------+-----------+
| department_id | median | employees |
+---------------+---------------+-----------+
| 1 | 142000.000000 | 4 |
| 2 | 99000.000000 | 3 |
| 3 | 80000.000000 | 2 |
+---------------+---------------+-----------+PARTITION BY department_idrestarts the numbering and the count for each department, so every row knows its position and its department's size.- Engineering has 4 salaries (118k, 142k, 142k, 185k). Rows 2 and 3 are both 142,000, so the median is
142000. - Sales has 3 (95k, 99k, 130k);
(3 + 1) / 2 = 2, so only row 2 is kept:99000. - Support has 2 (72k, 88k); rows 1 and 2 are averaged:
80000. MIN(cnt)is just a way to show the group size:cntis the same on every row of a group, and an aggregate is needed underONLY_FULL_GROUP_BY. Jonas (no department) is filtered out; without the filter he'd form his ownNULLgroup.
Learn more: Window functions: OVER and PARTITION BY.