Using JOIN with cte

November 9, 2022

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

>Solution :

You did not join the table.

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 e.department_id,
       d.department_name,
       e.first_name,
       e.last_name,
       e.salary
FROM   cte e
       INNER JOIN departments d
       ON (d.department_id = e.department_id)
WHERE  rnk=1

or:

WITH cte AS (
  SELECT e.department_id,
         d.department_name,
         e.first_name,
         e.last_name,
         e.salary,
         DENSE_RANK() OVER(PARTITION BY e.department_id ORDER BY e.salary DESC) AS rnk
  FROM   employees e
         INNER JOIN departments d
         ON (d.department_id = e.department_id)
)
SELECT department_id,
       department_name,
       first_name,
       last_name,
       salary
FROM   cte
WHERE  rnk=1

or using a sub-query, instead of the sub-query factoring clause:

SELECT e.department_id,
       d.department_name,
       e.first_name,
       e.last_name,
       e.salary
FROM   (
         SELECT department_id,
                first_name,
                last_name,
                salary,
                DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS rnk
         FROM employees
       ) e
       INNER JOIN departments d
       ON (d.department_id = e.department_id)
WHERE  rnk=1

or:

SELECT department_id,
       department_name,
       first_name,
       last_name,
       salary
FROM   (
  SELECT e.department_id,
         d.department_name,
         e.first_name,
         e.last_name,
         e.salary,
         DENSE_RANK() OVER(PARTITION BY e.department_id ORDER BY e.salary DESC) AS rnk
  FROM   employees e
         INNER JOIN departments d
         ON (d.department_id = e.department_id)
)
WHERE  rnk=1

fiddle