NEWS

Monday, May 13, 2013

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
01.declare  @number table(num integer
02. 
03.Insert into @number values(3456)
04.Insert into @number values(846)
05.Insert into @number values(1456)
06.Insert into @number values(3098)
07.Insert into @number values(34)
08.Insert into @number values(67856)
09.Insert into @number values(906)
10.Insert into @number values(34656)
11.Insert into @number values(9056)
12.Insert into @number values(3036)
(1) Use Inner Join
1.select t1.num from @number t1 inner join @number t2 on t1.num<=t2.num
2.group by t1.num having count(t1.num)=5
(2) Use Top Operator
1.Select top 1 num from
2.(
3.Select top 5 num from @number order by num desc
4.) T
5.order by num asc
(3) Generate Serial No based on descending order of the
1.select num from
2.(
3.Select (select count(*) from @number where num>=T.num)as Sno ,num
4.from @number as T
5.as temp
6.where Sno=5
(4) Generate Serial No based on descending order of the values in where Clause
1.select num from @number as n
2.where (select count(*) from @number where num>=n.num)=5
(5) Use Aggregate
1.Select min(num) from (select top 5 num from @number order by num desc) T
(6) Use Row_number() function
1.select num from
2.(
3.select num, row_number() over (order by num descas sno from @number
4.as t
5.where sno=5
6. 
7.Now you can replace to the number that you want to find the maximum value


General SQL query should be as follows

select sal from emp a where N = (select count(distinct sal) from emp b where a.sal <= b.sal) where N = any value.