Find Nth Highest Value – With Using Only Aggregate Function

Problem:

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 ?”

Solution:

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.

Use AdventureWorks

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