Find Nth ( 2nd, 3rd max ) maximum value in SQL Server

Find Nth maximum value in SQL Server

Use Pubs
Go

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)
Go

Insert into Employee values (1,'harry',3500)
Insert into Employee values (2,'jack',2500)
Insert into Employee values (3,'john',2500)
Insert into Employee values (4,'xavier',5500)
Insert into Employee values (5,'steven',7500)
Insert into Employee values (6,'susana',2400)
Go

 

 

Select * From Employee E1 Where
    (0) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)

Select * From Employee E1 Where
    (1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
....

....
http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server