Tuesday, March 13, 2012

Find Nth Highest Value – With Using Only Aggregate Function


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

No comments:

Post a Comment