Wednesday, June 28, 2023

How to find 2nd (or nth Highest Salary)

/* Formatted on 28/06/2023 20:03:03 (QP5 v5.336) */

--By Analytic Function 

select  *   from

(SELECT employee_id,

       first_name,

       salary,

       DENSE_RANK () OVER (ORDER BY salary DESC)  AS RANK

  FROM employees ) temp_emp

where temp_emp.RANK = 2 ;


-- By Group Function : 2nd Highest Salary

SELECT Max(salary) AS Salary

FROM   employees

WHERE  salary < (SELECT Max(salary)

FROM   employees) ;


/* Formatted on 28/06/2023 20:14:49 (QP5 v5.336) */

--By With Clause 

WITH  temp_emp   AS 

        (SELECT employee_id,

                first_name,

                Salary,

                DENSE_RANK () OVER (ORDER BY salary DESC) AS RANK

           FROM Employees)

SELECT *

  FROM temp_emp

 WHERE temp_emp.RANK = 2 ;

No comments:

Post a Comment