Today I came across a SQL Puzzle, “How to Find Nth Highest value without using any Built in ANSI Functions like Top, Rank() , Dense_Rank(), Row_number(), Etc., you can use only aggregate functions like Min, Max and Count ?”
The following queries will help you to identify Nth highest value with using Only Aggregate Functions.
Note: I’ve used Sales.SalesOrderDetail Table from adventure Works Database in my script, you can replace the column and table name as per your table structure.
--- To Find Nth Highest Value, Replace N with Some Number in the given Query, For Example, if you want to find 12th Highest value, Use 12 Instead of "N"
SELECT distinct UnitPrice
FROM Sales.SalesOrderDetail E1
WHERE N =
SELECT COUNT(DISTINCT E2.UnitPrice)
FROM Sales.SalesOrderDetail E2
WHERE E2.UnitPrice >= E1.UnitPrice
---- following Query will returns distinct values in Descending order, you can refer this Query’s output to verify Nth value of previous query output.
select distinct UnitPrice from Sales.SalesOrderDetail A
order by UnitPrice desc
Hope it Helps!