Order table by two columns-CodePudding

Imagine that I have a table with entities and some of them have forks; the column fork stores the id of the parent ent or 0, if that ent doesn't have a fork.

Table ents:

id  name        fork
1   Adidas      0
2   Betina      0
3   Shop Adidas 1
4   Shell       0
5   StoreAdidas 1
6   Shell 1     4

This is how I would like to get the results, ie, sorted by name but with the forks next with it's parent:

id  name
1   Adidas
3   Shop Adidas
5   StoreAdidas
2   Betina
4   Shell
6   Shell 1

Is this possible with a mySQL query?

Thank you for you help

CodePudding user response：

If you want to sort by name instead of by id, a self-join is needed:

select t1.id, t1.name
from table_name t1 left join table_name t2
on t1.fork = t2.id
order by coalesce(t2.name, t1.name)

Fiddle

CodePudding user response：

You can do:

select *
from t
order by case when fork <> 0 then fork else id end, 
         case when fork <> 0 then id else 0 end

See running example at DB Fiddle.