Monday, November 28, 2011

How to get Nth Highest Salary

--// Table Variable Creation
Declare @Employee Table
(
    EmpID            int NOT NULL,
    EmpName            nvarchar(50) NOT NULL,
    Designation        nvarchar(50) NOT NULL,
    Salary            money NOT NULL
)

--// Data Insertion
INSERT INTO @Employee (EmpID, EmpName, Designation, Salary) VALUES
  (1001, 'Anju', 'Manager', 8000.0000)
 ,(1002, 'Babu', 'Engineer', 9000.0000)
 ,(1003, 'Cenoy', 'Doctor', 7000.0000)
 ,(1004, 'Deepthi','Manager', 6000.0000)
 ,(1005, 'Emilin','Engineer', 5000.0000)
 ,(1006, 'Febin','Doctor', 8000.0000)
 ,(1007, 'Stephen','IT Consultant', 6000.0000)


--// Query to find Nth Largest Salary from the table.

Declare @Nth Int = 2

Select * From @Employee E1 Where @Nth = (Select Count(Distinct Salary) From @Employee E2 Where E1.Salary <= E2.Salary)

No comments:

Post a Comment