:::: MENU ::::

Finding Nth highest value in a column through T-SQL

  • Jan 07 / 2010
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Tips and Tricks

Finding Nth highest value in a column through T-SQL

Here is a situation, to find a specific highest value in a column. Most common example may be that of a salary column where one may need 7th highest salary.
To make short of this short problem i have selected pubs..Sales.qty column to work with.
Apply following query and you will get total 11 distinct values arranged

 
USE Pubs  
 GO  
 SELECT DISTINCT qty  
 FROM sales  
 ORDER by qty DESC  
 GO  

Now suppose we have to find 7th highest value in qty column. The simple query for this is

 SELECT min(qty)  
 FROM sales  
 WHERE qty IN  
 (SELECT distinct TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC)  
 GO  

So its done.
It is appropriate to mention here another query that i found on a blog.

 
SELECT TOP 1 qty  
 FROM ( SELECT DISTINCT TOP 7 qty  
 FROM sales  
 ORDER BY qty DESC) ABC  
 ORDER BY qty ASC  
 GO  

So in above queries you may find nth highest value where n is always greater than 1.
That is all for finding nth highest value in a column.

Consult us to explore the Databases. Contact us