Home » SQL & PL/SQL » SQL & PL/SQL » Display 3rd highest salary (join) (19c)
Display 3rd highest salary (join) [message #687951] |
Mon, 31 July 2023 09:04 |
|
Unclefool
Messages: 85 Registered: August 2021
|
Member |
|
|
I want to get the 3rd highest salary for each department. I'm almost there and know I need a join but I can't seem to figure out how to display the department_name with the output. Any help would be greatly appreciated.
Below is my test CASE and setup. Thanks to all who respond.
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', 110000, 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, 'Debra', 'Dante', DATE '2022-10-16', 102150,4 FROM DUAL UNION ALL
SELECT 11, 'Jerry', 'Torchiano', DATE '2022-10-30', 112660,4 FROM DUAL;
/* 3rd highest salary each dept */
select rnk, first_name, last_name, department_id, salary
from
(
select first_name, last_name, department_id, salary,
DENSE_RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk
from employees
)
where rnk = 3
|
|
|
Re: Display 3rd highest salary (join) [message #687952 is a reply to message #687951] |
Mon, 31 July 2023 09:11 |
|
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
select rnk, first_name, last_name, department_id, department_name, salary
from --- ^^ ADD ^^ ---
(
select first_name, last_name, department_id, salary,
DENSE_RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk
from employees
)
join departments using (department_id) -- <<< ADD <<< -----
where rnk = 3
|
|
|
|
Re: Display 3rd highest salary (join) [message #687954 is a reply to message #687953] |
Mon, 31 July 2023 10:22 |
|
mathguy
Messages: 108 Registered: January 2023
|
Senior Member |
|
|
Many (most?) programmers, for whatever reason, don't use the USING clause for joins. If you use an ON join condition instead, you do need to identify the tables or subqueries.
You don't need to alias the tables - you can use the table names instead. (Although aliasing is common, and makes for easier-to-read code.) But in any case, you need to give a name to the subquery.
You could rewrite the code like this:
select rnk, first_name, last_name, d.department_id, department_name, salary
from -- ^^ --- ^^ ADD ^^ ---
(
select first_name, last_name, department_id, salary,
DENSE_RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk
from employees
) q -- <<< ADD THE NAME q <<< -----
join departments d on q.department_id = d.department_id -- <<< ADD <<< -----
where rnk = 3
|
|
|
|
Re: Display 3rd highest salary (join) [message #687956 is a reply to message #687955] |
Mon, 31 July 2023 13:13 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Match recognize solution:
select 3 rnk,
m.first_name,
m.last_name,
m.department_id,
d.department_name,
m.salary
from employees
match_recognize(
partition by department_id
order by salary desc
measures
match_number() mn
all rows per match
pattern(
same_salary+
)
define same_salary as salary = first(salary) and match_number() <= 3
) m,
departments d
where m.department_id = d.department_id
and m.mn = 3
/
RNK FIRST_ LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME SALARY
---------- ------ --------- ------------- --------------- ----------
3 Cheryl Ford 1 IT 110000
3 Leslee Altman 1 IT 110000
3 Vicky Palazzo 2 Sales 68525
SQL>
SY.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:49:54 CDT 2024
|