SQL : Finding Nth highest salary in different ways

Query to find  2nd highest salary:

 

 1. Select max( salary) from emp where salary < (Select max(salary) from emp) 

 

 2. select  max(salary) from emp  where salary not in(select  max(salary) from emp)

 

You can write the query to find the Nth max salary details as follows, (This logic is called co-related sub queries, this acts as a for loop in a for loop.

In case of Sub Queries, the inner query gets executed only once. In co-related sub-queries for each record selected by outer query, inner query completely gets executed. So its time-complexity is O(n square)

 

Select * from Employee E1
where (N-1) = (Select count(distinct(E2.Salary)) From Employee E2
Where E2.salary > E1.Salary) 

 

ex:  to find 3rd highest salary 

Select * from Emp E1
where 2 = (Select count(E2.Salary) From Emp E2
Where E2.salary > E1.Salary)

 

 

The following SQL will also return the top ten employees by salary:

SELECT   EMPNO, LASTNAME, FIRSTNME, SALARY

FROM     

EMP A

WHERE 10 > (SELECT COUNT(*)

            FROM   EMP B

            WHERE  A.SALARY < B.SALARY

            AND    B.SALARY IS NOT NULL)

ORDER BY SALARY DESC;

 

 

another example: 

SELECT   distinct EMPid, SALARY

 

FROM     EMP A

 

WHERE 3 >(SELECT COUNT(*)

 

            FROM   EMP B

 

            WHERE  A.SALARY < B.SALARY

 

            AND    B.SALARY IS NOT NULL)

 

ORDER BY SALARY DESC;