Find Nth maximum,minimum value in SQL Server

By Guest Authors on 11 April 2004 | 14 Comments | Tags: SELECT

This aritlce is written by Hariharan Velayuthan. He writes "There are several methods to find out the Nth maximum/minimum value using SQL. This article discusses on such method to find Nth maximum value from a desired table. This article is aimed at users who are in the beginner or intermediate level in SQL Server."

[Note: This article assumes that the reader is familiar with the T-SQL, joins and queries]

I have taken utmost effort to make this article easy to understand, but incase you are not clear with the concept, please raise up your concern and I’ll be more than happy to attend your doubts. All the examples discussed in this article uses Employee table. If you do not have this table, please use the following script to create it.

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

A simple query that can find the employee with the maximum salary, would be:

Select * from Employee where salary = (Select max(Salary) from Employee)

How does this query work?

The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as:

Select * from Employee where salary = (7500)

Returns:

Eid Name Salary
5 steven 7500



find nth maximum salary

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

find nth minimum salary

Select * From Employee E1 Where
((select Count(Distinct(Salary)) from Employee) -n) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)