Finding nth highest & lowest salary example and explanation

Finding the nth highest &lowest salary in SQL have lots of query over the google,but i found good explanation here.

   
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

1. How to find 2nd highest or 3rd highest salary of an employee. 

   I am providing you a genral query to pick the nth highest salary from emp table it is not database specific query.

    select salary from emp e1 where (n-1)=(select count(*)  from emp where salary > e1.salary );

2.  You can also pick nth lowest salary of an employee.

     select salary from emp e1 where (n-1)=(select count(*)  from emp where salary < e1.salary ) ;

How does the query above work?

The query above can be quite confusing if you have not seen anything like it before – pay special attention to the fact that “Emp1″ appears in both the subquery (also known as an inner query) and the “outer” query. The outer query is just the part of the query that is not the subquery/inner query – both parts of the query are clearly labeled in the comments.

Understanding and visualizing how the query above works

Let’s assume that we are using this data:

Employee

Employee ID	Salary
3	200
4	800
7	450

3rd highest salary:

select * from Employeee as e1 where (N-1) =(select count(disticnt(e1.salary)

from emplyee as e2 where e2.salary>e1.salary)

select * from Employeee as e1 where (2) =(select count(disticnt(e1.salary)

from emplyee as e2 where e2.salary>200) 

Result:

2=3

select * from Employeee as e1 where (2) =(select count(disticnt(e1.salary)

from emplyee as e2 where e2.salary>800) 

Result:

2=1

select * from Employeee as e1 where (2) =(select count(disticnt(e1.salary)

from emplyee as e2 where e2.salary>450) 

Result:

2=2

Result is true so our o/p successfully got

O/p

450