Translate

Friday, September 20, 2013

SQL Query to find nth highest value in a database table

General query to find out nth highest value (here salary)is:

SELECT *
FROM Test_Table T1
WHERE ( n ) = (
                SELECT COUNT( DISTINCT ( T2.Salary ) )
                FROM Test_Table T2
                WHERE T2.Employee_Salary >= T1.Salary
            )

Here, you may give value for "n" to calculate the nth highest value.

How it works??
This query involves use of an inner query.
Inner queries can be of two types. 
  •  Correlated (where inner query runs in conjunction to outer query)
  • Uncorrelated (where inner query can run independently of outer query)
Above is the example of correlated query.
When the inner query executes every time, a row from outer query is processed. Inner query return the count of distinct  salaries which are higher than the currently processing row’s salary column. Anytime, it find that salary column’s value of current row from outer query, is equal to count of higher salaries from inner query, it returns the result.

Performance Analysis:

Inner query executes every time, one row of outer query is processed, this brings a lot of performance overhead, specially if the number of rows are too big.

To avoid this, one should use DB specific keywords to get the result faster. For example in SQL server, one can use key word TOP like this:

SELECT TOP 1 Salary
FROM
(
    SELECT DISTINCT TOP N Salary
    FROM Test_Table
    ORDER BY Salary DESC
) A
ORDER BY Salary


Here the value of "N" should be greater than 1.




**Have took help of Lokesh Gupta's article while writing this post.

1 comment:

Your Views