CODEWITHSUNDEEP
sqlpostgresql
poukpouk
poukpouk

Reputation: 15

How to get a data from the row of the last parent

I get a table looking like this:

enter image description here

If the id doesn't get a parent, I need to get "created_at" of it and if he got one, I need to get the "created_at" of the last parent. I tried left join, without success.

Expected results are:

Upvotes: 0

Views: 38

Answers (1)

LordF
LordF

Reputation: 439

Try this:

WITH RECURSIVE tree(id, created_at, parent_id) AS (
    SELECT id, created_at, parent_id
    FROM your_table
    WHERE parent_id IS NULL
    UNION ALL
    SELECT 
        c.id, 
        p.created_at, 
        c.parent_id
    FROM tree AS p, your_table AS c 
    WHERE c.parent_id = p.id
)
SELECT * FROM tree AS n

Look up at this fiddle: https://www.db-fiddle.com/f/xyqSDNapZBm6zd3eZ4wauo/1

Upvotes: 1

Related Questions