Consider the EmployeePosition table given below: EmpID EmpName DateOfJoining Salary --- ---...
Exl technical mcq question, verified with a worked answer. Free to practise - no sign-up.
Consider the EmployeePosition table given below:
| EmpID | EmpName | DateOfJoining | Salary |
|---|---|---|---|
| 1 | Paul | 01/05/2000 | 500000 |
| 2 | Alice | 02/05/2000 | 75000 |
| 3 | Mary | 01/05/2000 | 90000 |
| 4 | Lisa | 02/05/2000 | 85000 |
| 5 | Executive | 01/05/2022 | 300000 |
Write a query to find the third-highest salary from the EmployeePosition table.
Show answer & explanation
To find the Nth highest value, the standard approach using TOP is to first retrieve the top N values in descending order, and then retrieve the top 1 value from that subset in ascending order. This effectively isolates the smallest value among the top N, which is the Nth highest overall.
Step-by-step Derivation:
Step 1: Analyze the inner query: SELECT TOP 3 salary FROM EmployeePosition ORDER BY salary DESC. Based on the provided data (500000, 300000, 90000, 85000, 75000), this query returns the three highest salaries: {500000, 300000, 90000}.
Step 2: Analyze the outer query: SELECT TOP 1 salary FROM (...) AS emp ORDER BY salary ASC. This takes the result set from Step 1 and sorts it in ascending order: {90000, 300000, 500000}.
Step 3: The TOP 1 operator selects the first record from this ascending list, which is 90000.
Step 4: Verify against the data: The salaries are 500k (1st), 300k (2nd), and 90k (3rd). The result 90000 is correctly the third-highest salary.