By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE classif (id, type, priority) 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 union all
select 15 as id, 'process' as type, 'low' as priority from dual union all
select 15 as id, 'process' as type, 'none' as priority from dual union all
select 15 as id, 'process' as type, 'none' as priority from dual;
18 rows affected
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;
| ID | TYPE | PRIORITY |
|---|---|---|
| 1 | account | high |
| 12 | enrollment | medium |
| 15 | process | low |
| 4 | security | high |
| 8 | transform | none |
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
| ID | TYPE | PRIORITY |
|---|---|---|
| 1 | account | high |
| 12 | enrollment | medium |
| 15 | process | low |
| 4 | security | high |
| 11 | transform | none |
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
| ID | TYPE | PRIORITY |
|---|---|---|
| 1 | account | high |
| 12 | enrollment | medium |
| 15 | process | low |
| 4 | security | high |
| 11 | transform | none |