By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_table (person_id, term, term_order) As
SELECT 14627, 201030, 1 FROM DUAL UNION ALL -- Not this one
SELECT 14627, 201110, 2 FROM DUAL UNION ALL -- This one
SELECT 14627, 201510, 3 FROM DUAL UNION ALL -- Ignore this
SELECT 14702, 201030, 1 FROM DUAL UNION ALL -- This one
SELECT 28103, 201030, 1 FROM DUAL UNION ALL -- This one
SELECT 28103, 201230, 2 FROM DUAL UNION ALL -- Ignore this
SELECT 28103, 201240, 3 FROM DUAL UNION ALL -- Ignore this
SELECT 28103, 201310, 4 FROM DUAL UNION ALL -- Ignore this
SELECT 33634, 201040, 1 FROM DUAL UNION ALL -- This one
SELECT 33634, 201110, 2 FROM DUAL UNION ALL -- Not this one
SELECT 33634, 201130, 3 FROM DUAL UNION ALL -- Ignore this
SELECT 32356, 201510, 1 FROM DUAL UNION ALL -- Ignore this
SELECT 53303, 201030, 1 FROM DUAL UNION ALL -- Not this one
SELECT 53303, 201040, 2 FROM DUAL UNION ALL -- This one.
SELECT 53303, 201110, 3 FROM DUAL UNION ALL -- Not this one
SELECT 53303, 201140, 4 FROM DUAL -- Ignore this
;
16 rows affected
SELECT *
FROM (
SELECT *
FROM my_table
WHERE term IN (201030, 201040, 201110)
)
MATCH_RECOGNIZE (
PARTITION BY person_id
ORDER BY term_order
ALL ROWS PER MATCH
PATTERN ( ^ {- IS30? -} ANY_ROW )
DEFINE
IS30 AS term = 201030
)
| PERSON_ID | TERM_ORDER | TERM |
|---|---|---|
| 14627 | 2 | 201110 |
| 14702 | 1 | 201030 |
| 28103 | 1 | 201030 |
| 33634 | 1 | 201040 |
| 53303 | 2 | 201040 |
SELECT person_id,
CASE
WHEN term = 201030
THEN next_term
ELSE term
END AS term
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY term_order) AS rn,
LEAD(term, 1, term) OVER (PARTITION BY person_id ORDER BY term_order)
AS next_term
FROM my_table t
WHERE term IN (201030, 201040, 201110)
)
WHERE rn = 1;
| PERSON_ID | TERM |
|---|---|
| 14627 | 201110 |
| 14702 | 201030 |
| 28103 | 201030 |
| 33634 | 201040 |
| 53303 | 201040 |