Hi Gabor!

You can do it with recursive cte:

WITH RECURSIVE tree AS (
SELECT t.id, t.parent_id
FROM table1 t
WHERE t.parent_id = (select root.id from table1 root where root.parent_id is null)
UNION ALL
SELECT m.id, t.parent_id
FROM table1 m
JOIN tree t ON t.id = m.parent_id
WHERE m.parent_id is not null
ORDER BY m.id
)
select r.id, r.parent_id
from table1 r
where r.parent_id is null
union all
SELECT t.id, t.parent_id
FROM tree t


András

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, May 29, 2018 6:25 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

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


------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links





__________ Information from ESET Mail Security, version of virus signature database 17465 (20180529) __________

The message was checked by ESET Mail Security.
http://www.eset.com




__________ Information from ESET Mail Security, version of virus signature database 17465 (20180529) __________

The message was checked by ESET Mail Security.
http://www.eset.com