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 27 28 29 30
|
select Col1,col2,col3,
dense_rank() over(order by Col1) Num_occ
from
(
WITH Tab AS
(
SELECT 'a' AS col1 , 'a' AS col2 , 'c' AS col3 FROM dual
union all
SELECT 'g' AS col1 , 'b' AS col2 , 'd' AS col3 FROM dual
union all
SELECT 'c' AS col1 , 'b' AS col2 , 'e' AS col3 FROM dual
union all
SELECT 'a' AS col1 , 'a' AS col2 , 'c' AS col3 FROM dual
union all
SELECT 't' AS col1 , 'b' AS col2 , 'd' AS col3 FROM dual
union all
SELECT 'e' AS col1 , 'b' AS col2 , 'e' AS col3 FROM dual
)
select * from tab
)
COL1 COL2 COL3 NUM_OCC
---- ---- ---- ----------------------
a a c 1
a a c 1
c b e 2
e b e 3
g b d 4
t b d 5 |
Partager