WITH t AS ( -- 정렬 기준 필요합니다.(seq 추가) SELECT 0 seq, 'A' cd FROM dual UNION ALL SELECT 1, 'A' FROM dual UNION ALL SELECT 2, 'B' FROM dual UNION ALL SELECT 3, 'A' FROM dual UNION ALL SELECT 4, 'A' FROM dual UNION ALL SELECT 5, 'A' FROM dual UNION ALL SELECT 6, 'C' FROM dual UNION ALL SELECT 7, 'C' FROM dual UNION ALL SELECT 8, 'B' FROM dual UNION ALL SELECT 9, 'B' FROM dual ) SELECT seq, cd , ROW_NUMBER() OVER(PARTITION BY cd, gb ORDER BY seq) rn FROM (SELECT seq, cd , ROW_NUMBER() OVER(ORDER BY seq) - ROW_NUMBER() OVER(PARTITION BY cd ORDER BY seq) gb FROM t ) ORDER BY seq ;