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  Salary_ranks 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 Salary_ranks 
WHERE SalaryRank = 4;