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!
