## Find Nth Maximum value

**"How do I find Nth maximum value?"** is one of the most asked questions

Here are some methods

declare @number table(num integer)

Insert into @number values(3456)

Insert into @number values(846)

Insert into @number values(1456)

Insert into @number values(3098)

Insert into @number values(34)

Insert into @number values(67856)

Insert into @number values(906)

Insert into @number values(34656)

Insert into @number values(9056)

Insert into @number values(3036)

**(****1) Use Inner Join **

select t1.num from @number t1 inner join @number t2 on t1.num<=t2.num

group by t1.num having count(t1.num)=5

**(****2) Use Top Operator **

Select top 1 num from

(

Select top 5 num from @number order by num desc

) T

order by num asc

**(**

**3) Generate Serial No based on descending order of the**

**values**

select num from

(

Select (select count(*) from @number where num>=T.num)as Sno ,num

from @number as T

) as temp

where Sno=5

**(****4) Generate Serial No based on descending order of the values in where Clause**

where (select count(*) from @number where num>=n.num)=5

**(5) Use Aggregate ****Function**

**(6) Use Row_number() function**

(

select num, row_number() over (order by num desc) as sno from @number

) as t

where sno=5

Now you can replace 5 to the @number that you want to find the maximum value

## # re: Find Nth Maximum value

Hi,

Your way of explanition is simply great.Users will have good understanding while explaing the query using different operators. Good!

## # re: Find Nth Maximum value

Thanks Udayarekha

## # re: Find Nth Maximum value

Ecellent solutions!!!

## # re: Find Nth Maximum value

Thanks Ruby

## # re: Find Nth Maximum value

Hi Madhivanan,

Thanks for the excellent post.

Can you pls explain the meaning of T in this query ?

Select min(num) from (select top 5 num from number order by num desc) T

## # re: Find Nth Maximum value

Hi Danasegarane

It is the alias name for the derived table

The query select top 5 num from number order by num desc is a derived table from which data is retreived using min function

## # re: Find Nth Maximum value

1)Use Inner Join

this one is not working properly,I have checked it,but not working properly.if the value (salary) is same digits(100,200,300,400) of all rows mean its working fine,other wise the value(salary) is different digits(1000,200,30000,450) mean here not working fine.

Can you please check it and reply me.

Thanks and Regards,

Muthukrishnan.S

## # re: Find Nth Maximum value

Muthukrishnan,

What is the datatype of the column?

Can you try the example I have given?

## # re: Find Nth Maximum value

hi,

i got fair idea ...u explanation is simply great