By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH yourTable AS (
SELECT 1 AS case_id, '1-1' AS subcase_id FROM dual UNION ALL
SELECT 1, '1-2' FROM dual UNION ALL
SELECT 1, '1-3' FROM dual UNION ALL
SELECT 1, '1-6' FROM dual UNION ALL
SELECT 1, '1-10' FROM dual UNION ALL
SELECT 2, '2-1' FROM dual UNION ALL
SELECT 2, '2-7' FROM dual UNION ALL
SELECT 2, '2-23' FROM dual UNION ALL
SELECT 10, '10-1' FROM dual UNION ALL
SELECT 10, '10-10' FROM dual
)
SELECT
case_id,
subcase_id,
ROW_NUMBER() OVER (PARTITION BY case_id
ORDER BY TO_NUMBER(SUBSTR(subcase_id, 1, INSTR(subcase_id, '-') - 1)),
TO_NUMBER(SUBSTR(subcase_id, INSTR(subcase_id, '-') + 1))) rn
FROM yourTable
ORDER BY
case_id,
TO_NUMBER(SUBSTR(subcase_id, 1, INSTR(subcase_id, '-') - 1)),
TO_NUMBER(SUBSTR(subcase_id, INSTR(subcase_id, '-') + 1));
| CASE_ID | SUBCASE_ID | RN |
|---|---|---|
| 1 | 1-1 | 1 |
| 1 | 1-2 | 2 |
| 1 | 1-3 | 3 |
| 1 | 1-6 | 4 |
| 1 | 1-10 | 5 |
| 2 | 2-1 | 1 |
| 2 | 2-7 | 2 |
| 2 | 2-23 | 3 |
| 10 | 10-1 | 1 |
| 10 | 10-10 | 2 |