Home » SQL & PL/SQL » SQL & PL/SQL » JOIN with cte
JOIN with cte [message #686642] Tue, 08 November 2022 18:31 Go to next message
Unclefool
Messages: 65
Registered: August 2021
Member
I have the following setup, which seems to be working fine. I am having trouble modifying the query to include the department_name in the output.

I can't seem to get the JOIN working with the CTE. Its probably something trivial but after many attempts I can't get it to work.

Any help would be appreciated.

Below is my setup and test case.


CREATE TABLE departments(  department_id, department_name) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 2, 'DBA' 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', 100000, 1 FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04', 50000, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60000, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70000,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 88000,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10', 666666, 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;

WITH cte AS (
    SELECT department_id,
                  first_name,
                  last_name,
                  salary,
            DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rnk
        FROM employees
                  
)
 SELECT department_id,
 /* department_name  */
            first_name,
            last_name,
            salary
    FROM cte
    WHERE rnk=1

Re: JOIN with cte [message #686643 is a reply to message #686642] Wed, 09 November 2022 01:25 Go to previous message
Michel Cadot
Messages: 68342
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just join cte as you'd do it with employee in the main query.
Previous Topic: Pivot query
Next Topic: Dynamic Procedure To Drop A Database Link
Goto Forum:
  


Current Time: Mon Jan 30 22:07:21 CST 2023