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.

[code lang=”sql”]

–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

[/code]

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

[code lang=”sql”]

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

[/code]