oracle sql grouping and custom ordering

Below is source data

Due Date    Status       Activity Name               Completed_date
            Completed    Maintenance (ADV)              23-Feb-23
9-Apr-23    Assessment   Maintenance (ADV)  
            Completed    Records Management Standards   10-Mar-23
16-Apr-23   Pending      Records Management Standards   
16-Apr-23   Assessment   Records Management Standards   
10-Mar-23   Scheduled    Records Management Standards   
            Completed    Monitor Compliance             14-Feb-23
9-Apr-23    Assessment   Monitor Compliance
            Completed    Monitor Customer               23-Feb-23
9-Apr-23    Pending      Monitor Customer   

And what i need is

Due Date    Status          Activity Name             Completed_date
9-Apr-23    Completed   Maintenance (ADV)             23-Feb-23
16-Apr-23   Completed   Records Management Standards  10-Mar-23
9-Apr-23    Completed   Monitor Compliance            14-Feb-23
9-Apr-23    Completed   Monitor Customer              23-Feb-23

I wrote 2 queries one to get the max of due date, completed date grouping them by activity name

select MAX(DUE_DATE) as due_date, ACTIVITY_NAME, MAX(COMPLETED_DATE) as completed_date from activity_history group by ARCM_ACTIVITY_NAME

And one more for custom sorting on the status column

select t.*
from (select R.*,
             row_number() over (partition by ACTIVITY_NAME
                                order by (case when status = 'Completed' then 1
                                               when status = 'Scheduled' then 3
                                               else 2
                                          end)) as rank
      from activity_history  R
     ) t
where rank = 1;

How do i merge this in to single query to get the above desired output?

>Solution :

You can aggregate and use MAX(...) KEEP (DENSE_RANK FIRST...):

SELECT MAX(due_date) AS due_date,
       MAX(status) KEEP (
         DENSE_RANK FIRST
         ORDER BY CASE status
                  WHEN 'Completed' THEN 1
                  WHEN 'Scheduled' THEN 3
                                   ELSE 2
                  END ASC,
                  COALESCE(due_date, completed_date) DESC
       ) AS status,
       activity_name,
       MAX(completed_date) AS completed_date
FROM   table_name
GROUP BY activity_name

Which, for the sample data:

CREATE TABLE table_name (Due_Date, Status, Activity_Name, Completed_date) AS
SELECT NULL,              'Completed',  'Maintenance (ADV)',             DATE '2023-02-23' FROM DUAL UNION ALL
SELECT DATE '2023-04-09', 'Assessment', 'Maintenance (ADV)',             NULL FROM DUAL UNION ALL
SELECT NULL,              'Completed',  'Records Management Standards',  DATE '2023-04-10' FROM DUAL UNION ALL
SELECT DATE '2023-04-16', 'Pending',    'Records Management Standards',  NULL FROM DUAL UNION ALL
SELECT DATE '2023-04-16', 'Assessment', 'Records Management Standards',  NULL FROM DUAL UNION ALL
SELECT DATE '2023-03-10', 'Scheduled',  'Records Management Standards',  NULL FROM DUAL UNION ALL
SELECT NULL,              'Completed',  'Monitor Compliance',            DATE '2023-02-14' FROM DUAL UNION ALL
SELECT DATE '2023-04-09', 'Assessment', 'Monitor Compliance',            NULL FROM DUAL UNION ALL
SELECT NULL,              'Completed',  'Monitor Customer',              DATE '2023-02-23' FROM DUAL UNION ALL
SELECT DATE '2023-04-09', 'Pending',    'Monitor Customer',              NULL FROM DUAL;

Outputs:

fiddle