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
Create table number (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 T ) temp where Sno=5
(4) Use Aggregate Function
Select min(num) from (select top 5 num from number order by num desc) T
Now you can replace 5 to the number that you want to find the maximum value