Sorting data from one column to another

Question

I am setting up a hierarchy and everything is working correctly, I just have a small problem, in the ordering of the data.

Follows the structure of the table. (as basic as possible)

create table hierarquia (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hierarquia_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

and the data are these:

insert into hierarquia values
(1, 0), 
(2, 0), 
(3, 1), 
(4, 1),
(5, 0),
(6, 3);

When I do my query select * from hierarquia order by hierarquia_id, id does not return the data in the order I need.

ID | HIERARQUIA_ID
1  | 0
2  | 0 
5  | 0
3  | 1 
4  | 1
6  | 3

It would need to return the data more consistently ordered by their hierarchies, and it would have to return the data like this:

ID | HIERARQUIA_ID
1  | 0
3  | 1 
6  | 3
4  | 1
2  | 0 
5  | 0

First the first hierarchy without sub-hierarchy, the next being the sub-hierarchy of the first and consecutively.

There is this possibility in the mysql ?

Example in Sqlfiddle

Answer 1

Solution using stored Procedure

I found in SOEN, a solution based on stored Procedure, which does not require changing the structure of the bank:

https://stackoverflow.com/a/11035966/195417

Solution changing the seat structure

I once worked with this problem of sorting the records in the same order they appear in the tree, and the final solution was to create a new column in the database, with the address of each record inside the tree, in the form of the same string:

ID | HIERARQUIA_ID | ENDERECO
1  | 0             | "1"
2  | 0             | "2"
3  | 1             | "1\3"
4  | 1             | "1\4"
5  | 0             | "5"
6  | 3             | "1\3\6"

After sorting by the address column, would be in the order you want:

ID | HIERARQUIA_ID | ENDERECO
1  | 0             | "1"
3  | 1             | "1\3"
6  | 3             | "1\3\6"
4  | 1             | "1\4"
2  | 0             | "2"
5  | 0             | "5"

Sqlfiddle