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/