Retreive upper heirarchy

Question

I have Categories stored in a single table.

id  name    parentId    path    
A1  Cat 1   0           Cat 1
A2  Cat 2   A1          Cat 1 > Cat 2
A3  Cat 3   A2          Cat 1 > Cat 2 > Cat 3
A4  Cat 4   A3          Cat 1 > Cat 2 > Cat 3 > Cat 4
A5  Cat 5   A4          Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5
A6  Cat 6   A5          Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5 > Cat 6

I want to fetch all the upper hierarchy till I reach parent category id.

What I have achieved so far is:

with recursive cte (id, name, parentId, path) AS (
    select
        id,
        name,
        parentId,
        path
    from
        categories
    where
        parentId = 'A3'
    union
    all
    select
        c.id,
        c.name,
        c.parentId,
        c.path
    from
        categories c
        inner join cte on c.parentId = cte.id
)
select
    *
from
    cte;

Now the issue with above query is that I gives me lower hierarchy:

id  name    parentId    path
A4  Cat 4   A3  Cat 1 > Cat 2 > Cat 3 > Cat 4
A5  Cat 5   A4  Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5
A6  Cat 6   A5  Cat 1 > Cat 2 > Cat 3 > Cat 4 > Cat 5 > Cat 6

But what I am expecting is this:

id  name    parentId    path
A2  Cat 2   A1          Cat 1 > Cat 2
A1  Cat 1   0           Cat 1

I have created a reproducible scenario:

https://dbfiddle.uk/D3czXMX4

Answer 1

Your join with the parent is the wrong way around.

I also have changed the WHERE in the first part, as you requested all where the Parent = A3. If that was needed, you need to change it back ;-)

with cte (id, name, parentId, path, level) AS (
select
    id,
    name,
    parentId,
    path,
    1 as level
from
    categories
where
    id = 'A3'
union
all
select
    c.id,
    c.name,
    c.parentId,
    c.path,
    cte.level + 1
from
    categories c
    inner join cte on c.Id = cte.Parentid
)
select
    id, name, parentId, path, level
from
    cte
order by level DESC

Without order by:

With order by:

If you also would like to remove A3 from the resultset, you can filter this one out in your last select from the CTE:

select
    *
from
    cte
WHERE id <> 'A3'
order by path ASC