with t1
as
(
select 43247 ID , 46064 NBR, 'Red' CODE from DUAL UNION all
select 52395 , 52268 ,'Green' from DUAL UNION all
select 52760 , 52268 ,'Green' from DUAL UNION all
select 49025 , 52852 ,'Green' from DUAL UNION all
select 49025 , 52852 ,'Red' from DUAL UNION all
select 49025 , 52852 ,'Yellow' from DUAL UNION all
select 51681 , 72523 ,'Green' from DUAL UNION all
select 45202 , 131179 ,'Yellow' from DUAL UNION all
select 13127 , 440587 ,'Yellow' from DUAL UNION all
select 13128 , 440587 ,'Green' from DUAL
)
,
s1
as
(
select id, nbr, code, row_number() over (partition by nbr order by CASE CODE
WHEN 'Red' THEN 1
WHEN 'Yellow' THEN 2
WHEN 'Green' THEN 3
ELSE
4
END ) rnk
from t1
)
select id, nbr, code
from s1
where rnk = 1
order by id, nbr, rnk
/
select id, nbr, code
from
(
select id, nbr, code, row_number() over (partition by nbr order by CASE CODE
WHEN 'Red' THEN 1
WHEN 'Yellow' THEN 2
WHEN 'Green' THEN 3
ELSE
4
END ) rnk
from unicorns_and_rainbows
)
where rnk = 1
order by id, nbr, rnk
/
52395 52268 Green -OR- 52760 52268 Green (Can be one of them either by max NBR or ID)
Please post expected results based on your sample data.
You have two different ID's for NBR 440587, so what is returned?
What it there is a Red and Green but no yellow (and red yellow no green)?