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 27 August 2007 16:23 by Madhivanan
Filed under: ,

Comments

# re: Find Nth Maximum value

07 December 2007 09:19 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

07 December 2007 10:48 by Madhivanan

Thanks Udayarekha

# re: Find Nth Maximum value

09 January 2008 10:47 by Ruby

Ecellent solutions!!!

# re: Find Nth Maximum value

09 January 2008 12:48 by Madhivanan

Thanks Ruby

# re: Find Nth Maximum value

27 February 2008 09:49 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

27 February 2008 09:58 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

03 March 2008 12:55 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

15 March 2008 09:30 by Madhivanan

Muthukrishnan,

What is the datatype of the column?

Can you try the example I have given?

# re: Find Nth Maximum value

20 January 2009 07:15 by divyagr

hi,

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