SQL Top 2 problem.sql

/*
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