How to find Nth maximum or minimum value in SQL Server

Create Database Details

 

use NIR

Go

Create table Employee

(

Eid int,

Name varchar(10),

Salary money

)

Go

 

Insert into Employee values (1,'hari',3500)

Insert into Employee values (2,'ram',2500)

Insert into Employee values (3,'jai',2500)

Insert into Employee values (4,'shree',5500)

Insert into Employee values (5,'amit',7500)

Insert into Employee values (6,'sunil',2400)

Go

 

Query that can find the employee with the maximum salary, would be:

 

select * from employee where salary =(select max(salary) from employee)

 

If the same syntax is applied to find out the 2nd or 3rd or 4th level of salary, the query would
become bit complex to understand. See the example below: 

 

look at the query that captures the Nth maximum value:

Select * From Employee E1 Where

    (N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where

               E2.Salary > E1.Salary)

(Where N is the level of Salary to be determined)

by substituting a value for N i.e. 4,(Idea is to find the 4th maximum salary):

Select * From Employee E1 Where

    (4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where

               E2.Salary > E1.Salary)

the above query works in the same manner in Oracle and Sybase as well. Applying the
same logic, to find out the first maximum salary the query would be:

Select * From Employee E1 Where

    (1-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where

            E2.Salary > E1.Salary)

 

if you are able to understand this functionality, you can work out various other queries in the
same manner. For example..

You want 2^nd smallest salary then change they symbol and the N value here as:

 

Select * from employee e1 where

(2-1)=(select count(distinct(e2.salary)) from employee e2 where

       E2.salary<e1.salary)

 

Here is another way to write query to find second highest value of a column in a table

 

select top 1 salary from employee

where salary< (select max(salary) from employee)

order by salary desc

 

or using aggregate functions as:

 

SELECT Max(salary) FROM employee

WHERE salary NOT IN (SELECT MAX(salary) FROM employee);

 

Or

SELECT max( value1) FROM val WHERE value1 NOT IN( SELECT max(value1)
FROM val );

 

------------------------------------------

We can avoid TOP by using DENSE_RANK() in SQL Server 2005/2008, If its SQL Server 2000 then N-1
is the best way.

LOWESE 3Rd Salary :
-------------------
;WITH CTEs
AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY) 'Nth',* FROM SAMPLE1)

SELECT Names,Salary FROM CTEs WHERE Nth=3


HIGHEST 3Rd Salary :
-------------------
;WITH CTEs
AS(SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) 'Nth',* FROM SAMPLE1)

SELECT Names,Salary FROM CTEs WHERE Nth=3