1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
| WITH automobile AS
(
select 'AutoA' as nom, 'NOIR' as couleur from dual union all
select 'AutoB' , 'NOIR' from dual union all
select 'AutoB' , 'BLEU' from dual union all
select 'AutoC' , 'BLEU' from dual union all
select 'AutoD' , 'VERTE' from dual union all
select 'AutoE' , 'NOIR' from dual union all
select 'AutoE' , 'NOIR' from dual union all
select 'AutoE' , 'NOIR' from dual
)
SELECT nom, couleur,
row_number() over(PARTITION BY nom ORDER BY case couleur when 'NOIR' then 1 else 2 end ASC) rn,
dense_rank() over(PARTITION BY nom ORDER BY case couleur when 'NOIR' then 1 else 2 end ASC) dr
FROM automobile
WHERE couleur IN ('NOIR', 'BLEU')
ORDER BY nom ASC;
NOM COULEUR RN DR
AutoA NOIR 1 1
AutoB NOIR 1 1
AutoB BLEU 2 2
AutoC BLEU 1 1
AutoE NOIR 1 1
AutoE NOIR 2 1
AutoE NOIR 3 1 |
Partager