Find Nth Maximum value




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

Here are some methods

I explain how to find 5th Maximum value

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

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

(5) Use Aggregate Function

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

(6) Use Row_number() function

select num from
(
        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



Published Monday, August 27, 2007 4:23 PM by Madhivanan
Filed under: ,

Comments

# re: Find Nth Maximum value

Friday, December 7, 2007 9:19 AM by Udayarekha

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

Friday, December 7, 2007 10:48 AM by Madhivanan

Thanks Udayarekha

# re: Find Nth Maximum value

Wednesday, January 9, 2008 10:47 AM by Ruby

Ecellent solutions!!!

# re: Find Nth Maximum value

Wednesday, January 9, 2008 12:48 PM by Madhivanan

Thanks Ruby

# re: Find Nth Maximum value

Wednesday, February 27, 2008 9:49 AM by Danasegarane

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

Wednesday, February 27, 2008 9:58 AM by Madhivanan

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

Monday, March 3, 2008 12:55 PM by Muthukrishnan

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

Saturday, March 15, 2008 9:30 AM by Madhivanan

Muthukrishnan,

What is the datatype of the column?

Can you try the example I have given?

# re: Find Nth Maximum value

Tuesday, January 20, 2009 7:15 AM by divyagr

hi,

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