Skip to content

Instantly share code, notes, and snippets.

@zmts
Last active June 23, 2023 15:16
Show Gist options
  • Save zmts/483f5132d54d8f9c5d578b45752023b5 to your computer and use it in GitHub Desktop.
Save zmts/483f5132d54d8f9c5d578b45752023b5 to your computer and use it in GitHub Desktop.
SQL: Get item level (aka generation) from parent child hierarchy

SQL: get item level (aka generation) from parent child hierarchy

CREATE TABLE "public"."parent_child" (
    "id" int4 NOT NULL DEFAULT nextval('people_id_seq'::regclass),
    "first_name" varchar(100),
    "parent_id" int4,
    "city" varchar(100),
    PRIMARY KEY ("id")
);
id first_name parent_id city
1 alex washington
2 fred washington
3 zoe 1 texas
4 mary 1 texas
5 brit 2 new mexico
6 joe 2 new mexico
7 bill 4 new mexico
8 anna 4 new mexico
9 jessy 8 new mexico
10 kriss 4 new mexico
11 stephen 5 new mexico
12 june 6 new mexico
13 suzy 6 miami
14 josephine 9 miami
15 sarah 9 miami
16 joni 9 miami

Get all generations

WITH RECURSIVE generation AS (
    SELECT
    	id,
    	first_name,
    	city,
    	parent_id,
    	0 AS generation_number
    FROM parent_child
    WHERE parent_id IS NULL
 
UNION ALL 
    SELECT
    	child.id,
    	child.first_name,
    	child.city,
    	child.parent_id,
    	generation_number +1 AS generation_number
    FROM parent_child child
    JOIN generation g ON g.id = child.parent_id
)

SELECT * FROM generation
ORDER BY generation;

Get some specific generation

WITH RECURSIVE generation AS (
    SELECT
    	id,
    	first_name,
    	city,
    	parent_id,
    	0 AS generation_number
    FROM parent_child
    WHERE parent_id IS NULL
 
UNION ALL 
    SELECT
    	child.id,
    	child.first_name,
    	child.city,
    	child.parent_id,
    	generation_number +1 AS generation_number
    FROM parent_child child
    JOIN generation g ON g.id = child.parent_id
)


SELECT * FROM generation
WHERE city = 'texas'
AND generation_number = 1
ORDER BY generation_number;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment