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-supp...@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
------------------------------------
------------------------------------
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
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
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
