By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Metadata
(revno int, idno int, code varchar2(6))
;
INSERT ALL
INTO Metadata (revno, idno, code)
VALUES (1, 1, 'Person')
INTO Metadata (revno, idno, code)
VALUES (2, 1, 'Animal')
INTO Metadata (revno, idno, code)
VALUES (3, 1, 'Person')
INTO Metadata (revno, idno, code)
VALUES (4, 2, 'Person')
INTO Metadata (revno, idno, code)
VALUES (5, 3, 'Person')
INTO Metadata (revno, idno, code)
VALUES (6, 2, 'Animal')
INTO Metadata (revno, idno, code)
VALUES (7, 2, 'Animal')
SELECT * FROM dual
;
7 rows affected
WITH CTE AS (
SELECT revno,
LAG(revno) OVER (partition BY idno, code ORDER BY revno) AS prev
FROM Metadata
)
SELECT *
FROM CTE
WHERE revno IN (3, 4, 7)
ORDER BY revno
| REVNO | PREV |
|---|---|
| 3 | 1 |
| 4 | null |
| 7 | 6 |