By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (Case, Sequence, Value1, Value2 ) AS
SELECT 'AA1001', 672, 73, 195 FROM DUAL UNION ALL
SELECT 'AA1001', 711, 73, 185 FROM DUAL UNION ALL
SELECT 'AA1001-01', 680, 73, 185 FROM DUAL UNION ALL
SELECT 'AA1001-02', 685, 72, 185 FROM DUAL UNION ALL
SELECT 'AA1001-02', 699, 72, 182 FROM DUAL UNION ALL
SELECT 'AB1002', 676, 51, 36 FROM DUAL UNION ALL
SELECT 'AB1002-01', 701, 48, 39 FROM DUAL UNION ALL
SELECT 'AB1002-01', 719, 48, 35 FROM DUAL UNION ALL
SELECT 'AB1002-02', 707, 51, 38 FROM DUAL UNION ALL
SELECT 'AA1003', 655, 122, 416 FROM DUAL UNION ALL
SELECT 'AA1003', 683, 113, 416 FROM DUAL;
11 rows affected
SELECT *
FROM (
SELECT t.*,
ROW_NUMBER() OVER (
PARTITION BY
SUBSTR(Case, 3, 4)
ORDER BY
COALESCE(SUBSTR(Case, 8, 2), '00') DESC,
Sequence DESC
) AS rn
FROM table_name t
)
WHERE rn = 1;
| CASE | SEQUENCE | VALUE1 | VALUE2 | RN |
|---|---|---|---|---|
| AA1001-02 | 699 | 72 | 182 | 1 |
| AB1002-02 | 707 | 51 | 38 | 1 |
| AA1003 | 683 | 113 | 416 | 1 |