I have a query to find the youngest generation in a family tree. This is my database
ID NAME PARENT_ID 1 A 0 2 B 1 3 C 1 4 D 2 5 E 3 6 F 3 7 G 6
I need an output like below if result has more than one row
NAME GENERATIONS E 4 F 4
or if the result returns single row
NAME GENERATIONS G 5
This is my query:
WITH RECURSIVE children AS ( SELECT id, name, parent_id FROM family UNION ALL SELECT f.id, f.name, f.parent_id FROM family f INNER JOIN children c ON c.id = f.parent_id ) SELECT ( SELECT name FROM family WHERE id IS NOT NULL AND parent_id = ( SELECT MAX(parent_id) as maxpi FROM family ) ) AS name, COUNT(DISTINCT main.parent_id) AS generations FROM family main ORDER BY name
How to fix this? And another question is there a way to get result using window funtion?
Advertisement
Answer
Calculate the generation as you recurse, and then get the rows that match the max(generation)
.
WITH RECURSIVE children AS ( SELECT id, name, parent_id, 1 as generation FROM family WHERE parent_id not in (SELECT id FROM family) UNION ALL SELECT f.id, f.name, f.parent_id, c.generation + 1 as generation FROM family f INNER JOIN children c ON c.id = f.parent_id ) SELECT * FROM children WHERE generation = (select max(generation) from children);