SQL- Nth Highest Salary

Q How to find the nth highest salary from a table in SQL Server ?
     Consider below the EmployeeDetails table.

EmployeeDetails Table

Let us target to find 2nd highest salary from above table which is 40000.
We can achieve this in the following two ways:

By using TOP clause

SELECT TOP 1 Salary  FROM  
   (SELECT DISTINCT TOP 2 Salary
       FROM EmployeeDetails 
       ORDER BY Salary DESC) A
      ORDER BY Salary 

Using TOP clause

Generalized query to find nth highest salary:

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

N --> Nth Highest Salary. Suppose you want 4th highest salary then                       substitute N= 4

    Sometimes an interviewer ask in other way like how to get the nth lowest salary from a table.Well, this can be answered by slightly in changing in above query.

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

The below snapshot explains the same.

2nd Lowest salary

USING CORRELATED SUBQUERY

SELECT Name, Salary    
FROM EmployeeDetails E1
WHERE 1= (SELECT COUNT(DISTINCT Salary) 
          FROM EmployeeDetails E2
               WHERE E2.Salary > E1.Salary)

Using Correlated Subqueries

Here , each salary of EmployeeDetails E1 table is compared with all the salary of EmployeeDetails E2 table and then count is calculated. Calculated count is matched with the digit in WHERE clause . As and when calculated count matches with the digit in WHERE clause , corresponding Name and Salary is displayed.

Generalized query to find nth highest salary:

SELECT Name, Salary    
FROM EmployeeDetails E1
WHERE (N-1)= (SELECT COUNT(DISTINCT Salary) 
          FROM EmployeeDetails E2
          WHERE E2.Salary > E1.Salary)


(N-1) --> Nth Highest Salary. Suppose you want 4th highest salary then                       substitute (N-1)=3


By using DENSE RANK function

SELECT Name, Salary 
FROM (
                SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) As                             Ranking FROM EmployeeDetails
              ) WHERE Ranking =2

Generalized query to find nth highest salary:

SELECT Name, Salary 
FROM (
                SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) As                             Ranking FROM EmployeeDetails
              ) WHERE Ranking = N

(N) --> Nth Highest Salary. Suppose you want 4th highest salary then substitute N= 4


Q. What is  the order of execution of WHERE, ORDER BY, GROUP BY, HAVING     in SQL SERVER.?
         In SQL SERVER, the order of execution are as follows:

          1.WHERE: It is the WHERE clause which gets executed first in SELECT                query to filter the rows in a table.    
          2.GROUP BY:Secondly GROUP BY clause gets executed. It groups the                rows filtered out by WHERE clause.
          3.HAVING:After GROUP BY clause, HAVING clause is executed. It filters              the grouped rows.
          4.ORDER BY : Finally ORDER BY clause is executed which orders the                  group