Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Find Nth Maximum value

Aug 27 2007 9:30AM by Madhivanan   

"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

Tags: Nth_maximum, t-sql, sql_server,

2 · 40% · 13039



  • Nice way to find the Nth Maximum value using join....Thanks madhi..And I need one clarification. Join query does not return the highest value if there is some replication in the data.

    For example I have the following data 95,90,78,96,85,90,98.In this 90 is replicated. I want to find the 4 th largest value in this.When I executed your join query,it does not work. I got "no data found". So can you please update the join query in this post to make sense.

    Thanks Prakash K

    commented on Jun 15 2011 12:58AM
    3163 · 0% · 2
  • You need to use (select distinct num from @number) in place of @number in the first query

    commented on Jun 15 2011 2:08AM
    2 · 40% · 13039
  • please help me in finding top salary among 2 table ? I have 2 table with salary columns

    select max(sal)from table1
    select max(sal)from table2

    I'm getting 2 outputs,but I want one,please help me . I'm a new to SQL.

    commented on Mar 24 2013 9:54PM
    1093 · 0% · 24
  • ani, you can make use of derived table

    select max(sal) as sal from
    select max(sal) as sal from table1
    select max(sal) as sal from table2
    ) as t
    commented on Mar 25 2013 12:42PM
    2 · 40% · 13039
  • Thanks that helps.

    commented on Mar 25 2013 9:43PM
    1093 · 0% · 24
  • Nth max salary you showed is for one table,if we are 2 different table how do you do. Please can you show them also. Thanks

    commented on Mar 25 2013 10:17PM
    1093 · 0% · 24
  • This comment is waiting for moderation.

    commented on Jan 22 2015 7:25AM
    3163 · 0% · 2

Your Comment

Sign Up or Login to post a comment.

"Find Nth Maximum value" rated 5 out of 5 by 3 readers
Find Nth Maximum value , 5.0 out of 5 based on 3 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]