Hi All,

I have a table (see below) with some parent/child data. I want to know
very top parent of every child. So the expected result (with below test
data) is:

ID,TOP_PARENT_ID
1,NULL
2,1
3,1
4,1
5,1
6,1

Is this result can be produced with a recursive CTE? (I tried already
without success.) Or use a recursive stored function (I use 3.0) for it?


CREATE TABLE TABLE1 (ID BIGINT NOT NULL, PARENT_ID BIGINT);

INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (1, NULL);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (2, 1);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (3, 2);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (4, 1);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (5, 4);
INSERT INTO TABLE1 (ID, PARENT_ID) VALUES (6, 1);

Gabor