Find the second highest salary for each department

Hello Friends,
Here I post one TSQL Challenge. I hopes it will help you.

TSQL Beginner’s Challenge #1 – Find the second highest salary for each department

http://beyondrelational.com/blogs/tcb/archive/2009/10/13/tsql-beginner-s-challenge-1-find-the-second-highest-salary-for-each-department.aspx

TSQL Challenge Solved-

TSQL:-
SET NOCOUNT ON
DECLARE @Employees TABLE(
EmployeeID INT IDENTITY,
EmployeeName VARCHAR(15),
Department VARCHAR(15),
Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary)
VALUES('T Clerk','Back-Office', 10000)


SELECT * FROM @Employees

;WITH SecondHighestSalary
AS
(
SELECT EmployeeID,EmployeeName,Department,SALARY AS SALARY,RANK() over (partition by Department order by salary) AS SalaryNumber FROM @Employees
)
SELECT EmployeeID,EmployeeName,Department,SALARY FROM SecondHighestSalary WHERE SalaryNumber=2



OUTPUT:-