WITH t AS ( SELECT 'A01' aid, 'K01' bid UNION ALL SELECT 'B01', 'X01' UNION ALL SELECT 'A01', 'X01' UNION ALL SELECT 'A02', 'X01' UNION ALL SELECT 'A04', 'X01' ) SELECT aid, bid , ROW_NUMBER() OVER(PARTITION BY bid, SUBSTR(aid, 1, 1) ORDER BY aid) - CASE COUNT(*) OVER(PARTITION BY bid, SUBSTR(aid, 1, 1)) WHEN 1 THEN 1 ELSE 0 END AS numid FROM t ;