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??
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.
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)
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.