Q:

Recursive query mysql

I am not able to get recursive data with a query that uses this statement:
WITH RECURSIVE
I'm trying to get the rows related on the table:
id |      name     |extern|
--------------------------|
1  |  building_01  |  null
--------------------------|
2  |    floor_01   |  1
--------------------------|
3  |    zone_04    |  2
--------------------------|
4  |    zone_05    |  3

I need to get this data: where are zone_05 ?
4 | zone_05    | 3
---------------|
3 | zone_04    | 2
---------------|
2 | floor_01   | 1
---------------|
1 | building_01| null
---------------|

I'm trying to solve it with this statement, but I only get the first related row :
select  id,
    name,
    extern
    from    (select * from table) products_sorted,
    (select @pv := '4') initialisation
    where   find_in_set(id, @pv) > 0
    and     @pv := concat(@pv, ',', extern)

Result:
4 | zone_05    | 3
---------------|
3 | zone_04    | 2
---------------|

This solution is not working for me :
How to create a MySQL hierarchical recursive query
@trincot Would it be possible to alter this to work in "reverse"? So grab all of a rows parents, grandparents, etc? I've used your first query for getting descendants but I would like to get the ancestors? as well. – shreddish Jul 19 '17 at 14:54
if anyone else was looking for the answer to the question @shreddish asked, the solution is to change on p.parent_id = cte.id to on p.id = cte.parent_id – fanfare Dec 10 '17 at 5:38
The solution is related to :
Alternative 1: WITH RECURSIVE, CONNECT BY
and I'm not able to use it because mysql database server version.

A:

This is the query that you need;
select  id,
        name,
        extern_id 
from    (select * from places
         order by id desc, extern_id desc) places_sorted,
        (select @pv := '26') initialisation
where   find_in_set(id, @pv) > 0
and     @pv := concat(@pv, ',', extern_id)

This is the fiddle: 
http://sqlfiddle.com/#!9/ba5700/2
As you can see, the "order by" is important as explained by trincot in the answer that you mention

