Sorting data from one column to another

Question

Sorting data from one column to another

Navigation

#1 by (2 votes)

2

I'm setting up a hierarchy and everything is working correctly, I just have a little problem sorting the data.

The table structure follows. (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 is 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

You would need to return the data more consistently ordered by your hierarchies, and you would have to return the data as follows:

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 ?

SQLFiddle Example

mysql

asked by anonymous 18.03.2014 / 18:20

1 answer

Ruby and Ruby on Rails version - Openshift What is the best way to create PHP Related Posts?

score 2 · 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 database:

link

Solution changing the bank structure

I once worked with this problem of sorting the records in the same order as 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 shape of the same string:

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

After sorting through the address column, it would stay in the order you want:

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