Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
Given a table of employees and salaries
find the second-highest paid employee
*/
DECLARE @Temp TABLE (EmployeeId INT, Salary INT)
INSERT INTO @Temp VALUES (1, 100000)
INSERT INTO @Temp VALUES (2, 110000)
INSERT INTO @Temp VALUES (3, 120000)
INSERT INTO @Temp VALUES (4, 130000)
INSERT INTO @Temp VALUES (5, 140000)
INSERT INTO @Temp VALUES (6, 150000)
/* Solution 1 */
SELECT Top 1 Top2.EmployeeId, Top2.Salary
FROM
@Temp T
INNER JOIN (SELECT TOP 2 EmployeeId, Salary
FROM
@Temp T1
ORDER BY
Salary DESC) AS Top2 ON T.EmployeeId = Top2.EmployeeId
ORDER BY
Top2.Salary ASC
/* Solution 2 */
;WITH Top2 AS
(
SELECT TOP 2 *
FROM
@Temp T1
ORDER BY
Salary DESC
)
SELECT TOP 1*
FROM
Top2
ORDER BY
Salary ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment