Jun 10, 2012

Find Nth Highest Salary of Employee

1 comment
This is most popular question and see how can we solve this. There may be many ways to achieve this. Bur here i am providing solution in two different ways below,


1. First we will go with using simple sub-query.


SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
       ORDER BY salary DESC
)
ORDER BY salary

where n > 1 (n is always greater than one)

Below is the example to get the 2nd highest salary of employee using above generic query,
SELECT TOP 1 salary
FROM ( 
      SELECT DISTINCT TOP 2 salary 
      FROM employee
      ORDER BY salary DESC
     )
ORDER BY salary


2.
SELECT
FROM Employee E1
WHERE (N-1) = ( 
            SELECT COUNT(DISTINCT(E2.Salary))
            FROM Employee E2
            WHERE E2.Salary > E1.Salary
              )
Where N is the level of Salary to be determined.
In the above example, the inner query uses a value of the outer query in its filter condition.i.e., the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row.

1 comment :