In MySQL, you want the salary that appears in the nth row after sorting all…
2024
In MySQL, you want the salary that appears in the nth row after sorting all salaries from the Employees table in descending (highest-first) order, where n is a positive integer chosen at run time.
Several of the options compute an offset into a session variable and then run a prepared statement that binds that value into the LIMIT clause (MySQL allows LIMIT parameters as ? placeholders in a prepared statement, but not as bare arithmetic expressions). Which option returns exactly the nth-highest salary?
- A.
SET @off = n;
PREPARE stmt FROM
'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1';
EXECUTE stmt USING @off; - B.
SET @off = n - 1;
PREPARE stmt FROM
'SELECT SALARY FROM Employees ORDER BY SALARY ASC LIMIT ?, 1';
EXECUTE stmt USING @off; - C.
SET @off = n - 1;
PREPARE stmt FROM
'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT 1, ?';
EXECUTE stmt USING @off; - D.
SET @off = n - 1;
PREPARE stmt FROM
'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1';
EXECUTE stmt USING @off; - E.
SELECT MAX(SALARY)
FROM Employees
WHERE SALARY < n;
Attempted by 14 students.
Show answer & explanation
Correct answer: D
MySQL's LIMIT clause takes the form LIMIT offset, row_count: offset is 0-based and tells the server how many rows to skip before it starts returning rows, and row_count is how many rows to return after that skip. To pick out the single row sitting at a given 1-based rank in a sorted result, you must skip (rank - 1) rows and return exactly 1 row - using the rank itself as the offset skips one row too many.
Applying this here: ORDER BY SALARY DESC places the highest salary in row 1, the second-highest in row 2, and so on, so the nth-highest salary sits at 1-based rank n. Reaching it needs offset = n - 1 and row_count = 1. LIMIT does not accept an arithmetic expression (you cannot write LIMIT n-1, 1 literally - MySQL requires LIMIT arguments to be nonnegative integer constants, ? placeholders inside a prepared statement, or integer variables inside a stored program), so the offset must be precomputed and bound:
SET @off = n - 1;
PREPARE stmt FROM
'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1';
EXECUTE stmt USING @off;
Quick check with n = 1: @off = 0, so the executed statement is effectively LIMIT 0, 1, which skips 0 rows and returns the single highest salary - exactly the 1st row, as expected.
Why the other approaches fail: binding the offset to n instead of n-1 skips one row too many and returns the (n+1)th-highest salary; sorting ascending counts up from the smallest salary instead of down from the largest; binding the value to the row count instead of the offset returns a block of rows rather than a single one; and comparing SALARY against the literal value n treats n as a salary amount rather than a row position.