How will you find 3rd employees from the top with highest salary from each department

Question

How will you find 3rd emplyees from top with highest salary in each department in Oracle SQL?

Select * 
from 
    (select 
         emp-id, dept_id, 
         row_number (order by salary desc) rn 
     from 
         employee 
     group by 
         emp_id, dept_id, salary) 
where rn = 3

Use a partition: e.g. `row_number() over(partition by dept_id order by salary DESC)` also consider use of dense_rank() instead of row_number() if you want "equal 3rd" — Paul Maxwell, Jul 31 '23 at 05:31

score 0 · Answer 1 · answered Jul 31 '23 at 05:45

The SQL query you offered appears nearly accurate, but there are a couple of issues. The GROUP BY clause is unnecessary, and you must consider the rank (rn) within each department separately. You can accomplish this by utilizing the PARTITION BY clause along with the ORDER BY clause inside the ROW_NUMBER() function. Here's the rectified SQL query:

SELECT *
FROM (
    SELECT
        emp_id,
        dept_id,
        salary,
        ROW_NUMBER() OVER (PARTITIONED BY dept_id ORDER BY salary DESC) AS rn
    FROM
        employee
) ranked_employee
WHERE rn = 3;

In this query, the ROW_NUMBER() function generates a unique rank for each row within each department based on the descending order of salary. The PARTITIONED BY dept_id ensures that the ranking is done separately for each department. Then, in the outer query, we select the rows where the rank (rn) is equal to 3, which corresponds to the third employee from the top with the highest salary in each department.

Beefstu · Answer 2 · 2023-07-31T20:38:18.807

Use the analytic RANK ir DENSE_RANK function depending how you want to treat ties.


CREATE TABLE departments(  department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'Sales' FROM DUAL UNION ALL
SELECT 3, 'Marketing' FROM DUAL UNION ALL
SELECT 4, 'Finance' FROM DUAL;

CREATE TABLE employees (employee_id, first_name, last_name, hire_date, salary,  department_id) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 160000, 1 FROM DUAL UNION ALL
SELECT 2, 'Sandy', 'Herring', DATE '2011-08-04', 150200, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2019-03-05', 60700, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2007-11-11', 70125,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2004-09-17', 68525,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2020-05-10', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2008-12-10', 66666, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL UNION ALL
SELECT 10, 'Silvio', 'Dante', DATE '2022-10-16', 102150,4 FROM DUAL UNION ALL
SELECT 11, 'Jerry', 'Torchiano', DATE '2022-10-30', 112660,4 FROM DUAL;


select  rnk, last_name, department_id, salary 
from    (
    select  last_name, department_id, salary,
        RANK () OVER ( PARTITION BY  department_id
                   ORDER BY      salary DESC
                 ) AS rnk
    from    employees
    )
where   rnk = 3


RNK LAST_NAME DEPARTMENT_ID SALARY
3 Ford 1 110000
3 Palazzo 2 68525

I would use the `dense_rank` analytic function instead of `rank`, because if there are, say, 2 people with the second highest salary, you wouldn't get a rank of 3. See https://dbfiddle.uk/_p6JggFi for an example. — Boneist, Jul 31 '23 at 08:41
This forum is made up of volunteers who try to help others by donating their time and expertise. Having said that, it would be nice to know if their solutions helped solve your issue. If it didn't please explain why and add more details. If it did, please upvote and accept an answer. — Beefstu, Aug 01 '23 at 16:15

How will you find 3rd employees from the top with highest salary from each department

2 Answers2