"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
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
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