160 ) Find the 4th Highest Salary

 

1) Find the 4th Highest Salary 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Question: Write a query to find the 4th highest salary from an Employee table.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Query:

1 ) row_number( ) : will give all sequential numbers for the  ranks even if there is a duplicate rank 

  --  Next  rank = next sequential rank as per soted order 

 

2 ) Rank( ) : if duplicate rank then 

--- Next rank =  ( total previous  dup. ranks ) + prev rank + 1 ) 

 

3 ) Dense rank ( )  :  if duplicate rank then 

----Next rank = (previous rank / duplicate rank )  + 1 

------------------------------------------------------------------------------------------------------------------

 -------To create temp table for Rank based on Salaries ----------------------

------------------------------------------------------------------------------------------------------------------

WITH  Temp_Salary AS (

    SELECT     EmployeeID,      Salary,

       DENSE_RANK() OVER (ORDER BY Salary DESC) as SalaryRank

    FROM Employees

)

------- ** To show emplds with 4th highest salary ----------------------

SELECT  empid FROM Temp_Salary 

WHERE SalaryRank = 4;