Find Nth Maximum value

“Find Nth Maximum value” is one of the very popular interview questions.

There are several methods to find out the answer. Here are some methods

Consider the following data set


use tempdb 

create table numbers (number integer)
Insert into numbers values(1288)
Insert into numbers values(8653)
Insert into numbers values(1)
Insert into numbers values(390)
Insert into numbers values(4009)
Insert into numbers values(345)
Insert into numbers values(2777)

Assume that you want to find out 3rd maximum value, use one of the following methods
(1) Use Inner Join

select
	t1.number from numbers t1 inner join numbers t2
	on t1.number<=t2.number  group by t1.number having count(t1.number)=3 

(2) Use Top Operator

 Select top 1 number from (      Select  		top 3 number  	from  		numbers  	order by number desc ) T  order by number asc  

(3) Generate Serial No based on descending order of the values

 select number from  (          Select  			( 				select  					count(*)  				from  					numbers  				where number>=T.number)as Sno ,
		number
        from numbers as T
) as temp
where Sno=3

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

select number from numbers as n
where
(
	select
		count(*)
	from
		numbers
	where number>=n.number
)=3

(5) Use Aggregate Function

Select min(number) from
(
	select
		top 3 number
	from
		numbers
	order by number desc
) T

(6) Use Row_number() function

select number from
(
    select
		number, row_number() over (order by number desc) as sno
	from
		numbers
) as t
where sno=3

Now you can replace 3 to the numbers that you want to find the maximum value

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s