Search this blog

Tuesday, March 13, 2012

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!

No comments:

Post a Comment