Query to group by a type considering a grouping with a priority scale

Question

I need to group some data based on their types and considering a priority scale.

Consider this CTE below as an example.

WITH classif AS
(
    select 1 as id, 'account' as type, 'high' as priority from dual union all
    select 2 as id, 'account' as type, 'none' as priority from dual union all
    select 3 as id, 'account' as type, 'medium' as priority from dual union all
    select 4 as id, 'security' as type, 'high' as priority from dual union all
    select 5 as id, 'security' as type, 'medium' as priority from dual union all
    select 6 as id, 'security' as type, 'low' as priority from dual union all
    select 7 as id, 'security' as type, 'none' as priority from dual union all
    select 8 as id, 'transform' as type, 'none' as priority from dual union all
    select 9 as id, 'transform' as type, 'none' as priority from dual union all
    select 10 as id, 'transform' as type, 'none' as priority from dual union all
    select 11 as id, 'transform' as type, 'none' as priority from dual union all
    select 12 as id, 'enrollment' as type, 'medium' as priority from dual union all
    select 13 as id, 'enrollment' as type, 'low' as priority from dual union all
    select 14 as id, 'enrollment' as type, 'low' as priority from dual union all
    select 15 as id, 'enrollment' as type, 'low' as priority from dual;
    select 15 as id, 'process' as type, 'low' as priority from dual;
    select 15 as id, 'process' as type, 'none' as priority from dual;
    select 15 as id, 'process' as type, 'none' as priority from dual;
)

For this data set, my output has to be like this

------------+-------------
type        |  priority
------------+-------------
account     |  high
security    |  high
transform   |  none
enrollment  |  medium
process     |  low
---------------------------

The scale for priority is from "high" to "none"

The rules for the output must be like this

• When a type has a line with priority "high", the output for that type must be "high".
• When a type has a line with priority "medium" and do not have other with "high", the output must by "medium".
• When a type has a line with priority "low" and do not have other with "high" or "medium", the output must by "low".
• When a type has a line with priority "none" and do not have any other, the output must be "none"

I'm trying to do something like this query below, but this will return all lines and not grouping according to priorities

select 
    type,
    case 
        when priority = 'high' then 'high'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
        when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
        end as priority        
from classif
group by type,
    case
    when priority = 'high' then 'high'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'medium' and priority <> 'high' then 'medium'
        when priority = 'low' and priority <> 'high' or priority <> 'medium' then 'low'
        when priority = 'none' and priority <> 'high' or priority <> 'medium' or priority <> 'low' then 'none'
        end;

Can you help me solve this in the query?

Answer 1

You can use ROW_NUMBER and order by a CASE statement to convert the priorities to a numerical value:

SELECT id, type, priority
FROM   (
  SELECT c.*,
         ROW_NUMBER() OVER (
           PARTITION BY type
           ORDER BY CASE priority
                    WHEN 'high'   THEN 1
                    WHEN 'medium' THEN 2
                    WHEN 'low'    THEN 3
                                  ELSE 4
                    END
         ) AS rn
  FROM   classif c
)
WHERE rn = 1;

Or, you can use MAX(...) KEEP (DENSE RANK FIRST ...) and order similarly using a CASE statement:

SELECT MAX(id) KEEP (
         DENSE_RANK FIRST
         ORDER BY CASE priority
                  WHEN 'high'   THEN 1
                  WHEN 'medium' THEN 2
                  WHEN 'low'    THEN 3
                                ELSE 4
                  END
       ) AS id,
       type,
       MAX(priority) KEEP (
         DENSE_RANK FIRST
         ORDER BY CASE priority
                  WHEN 'high'   THEN 1
                  WHEN 'medium' THEN 2
                  WHEN 'low'    THEN 3
                                ELSE 4
                  END
       ) AS priority
FROM   classif
GROUP BY type

Or, you can use a sub-query to store the relative priority levels and use a join:

SELECT MAX(c.id) KEEP (DENSE_RANK LAST ORDER BY p.id) AS id,
       type,
       MAX(c.priority) KEEP (DENSE_RANK LAST ORDER BY p.id) AS priority
FROM   classif c
       LEFT OUTER JOIN (
         SELECT 'high' AS priority, 3 As id FROM DUAL UNION ALL
         SELECT 'medium', 2 FROM DUAL UNION ALL
         SELECT 'low',    1 FROM DUAL UNION ALL
         SELECT 'none',   0 FROM DUAL
       ) p
       ON c.priority = p.priority
GROUP BY c.type

Which, for your sample data, all output:

ID	TYPE	PRIORITY
1	account	high
12	enrollment	medium
15	process	low
4	security	high
8	transform	none

db<>fiddle here