By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (c1 TEXT, c2 TEXT, c3 INT);
INSERT INTO test VALUES
('A','X',1),
('A','Y',2),
('B','X',3),
('B','Y',4),
('C','Z',5),
('D','X',6);
SELECT * FROM test;
| c1 | c2 | c3 |
|---|---|---|
| A | X | 1 |
| A | Y | 2 |
| B | X | 3 |
| B | Y | 4 |
| C | Z | 5 |
| D | X | 6 |
WITH RECURSIVE cte AS
( SELECT c1, c2, c3, 1 level
FROM test
UNION ALL
SELECT cte.c1, t2.c2, GREATEST(cte.c3, t2.c3), level+1
FROM cte
JOIN test t1 ON cte.c2 = t1.c2
JOIN test t2 ON t1.c1 = t2.c1
WHERE t2.c3 != cte.c3
AND level < ( SELECT MAX(c3)
FROM test )
UNION ALL
SELECT t2.c1, cte.c2, GREATEST(cte.c3, t2.c3), level+1
FROM cte
JOIN test t1 ON cte.c1 = t1.c1
JOIN test t2 ON t1.c2 = t2.c2
WHERE t2.c3 != cte.c3
AND level < ( SELECT MAX(c3)
FROM test )
)
SELECT cte.c1, cte.c2, MAX(cte.c3) c3
FROM cte
JOIN test ON (cte.c1, cte.c2) = (test.c1, test.c2)
GROUP BY c1, c2
HAVING c3 = MAX(test.c3);
| c1 | c2 | c3 |
|---|---|---|
| C | Z | 5 |
| D | X | 6 |