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.