SQL Query for ‘n’ th highest salary of employee table

IGate Recruitment, in written test has asked about to write an sql query for finding ‘n’ th highest salary from empolyee table. Here is a quick solution

Query for Creating Test Table and inserting dummy data.

--Creating Employee Table
--drop table Employee
CREATE TABLE Employee (EmpId BIGINT IDENTITY(10000,1) PRIMARY KEY, Salary MONEY)
go

-- Inserting dummy data - 2000 records are inserting with variable salary
DECLARE @counter INT
DECLARE @multiplier int
SET @counter = 10000
@multiplier = 33;

WHILE(@counter < 12000)
BEGIN

 INSERT INTO Employee(Salary) VALUES (@counter + (@counter - 1232 - 3232))
 -- just a trick of inserting some salary, diff for each employee.

 SET @counter = @counter +1
END

SQL Query for Retrieving the ‘n’ th largest salary from EMPLOYEE table.

SELECT  salary
FROM    Employee EM
WHERE   ( @n - 1 ) = ( SELECT   COUNT(*)
                       FROM     Employee 
                       WHERE    salary > EM.salary)