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?
Answer: D. SET @off = n - 1; PREPARE stmt FROM 'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1'; EXECUTE stmt USING @off; — ConceptMySQL's LIMIT offset, row_count form uses a zero-based offset: offset is the number of sorted rows skipped, and row_count is the number returned.…
- 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 139 students.
Show answer & explanation
Correct answer: D
Concept
MySQL's LIMIT offset, row_count form uses a zero-based offset: offset is the number of sorted rows skipped, and row_count is the number returned. Therefore, a one-based rank r maps to offset r - 1 when exactly one row is required.
Application
ORDER BY SALARY DESC arranges the salaries from highest to lowest, so the nth-highest salary occupies one-based position n.
Convert that one-based position to a zero-based offset: @off = n - 1.
Use LIMIT ?, 1 so the prepared statement binds @off as the offset and returns exactly one row: SET @off = n - 1; PREPARE stmt FROM 'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1'; EXECUTE stmt USING @off;
The resulting row is the salary at position n in the descending sequence.
Cross-check
For n = 1, @off = 0 and LIMIT 0, 1 returns the first, highest salary. For n = 2, @off = 1 and LIMIT 1, 1 skips only the highest salary and returns the second-highest salary.
Contrast
Using @off = n skips n rows, so the returned row occupies position n + 1.
Using ORDER BY SALARY ASC counts positions from the smallest salary upward.
Using LIMIT 1, ? binds n - 1 as the row count and returns a block after one skipped row.
Using WHERE SALARY < n treats n as a salary threshold rather than as a rank.
Thus the required statement is: SET @off = n - 1; PREPARE stmt FROM 'SELECT SALARY FROM Employees ORDER BY SALARY DESC LIMIT ?, 1'; EXECUTE stmt USING @off;