CODEWITHSUNDEEP
mysqlsorting
Mike
Mike

Reputation: 5818

MySQL Sorting by parents and children

Here a simple Table:

id, title, parent, ordering
---------------------------
3242, TitleB, 0, 1
6574, TitleBA, 3242, 1
2346, TitleBB, 3242, 2
2344, TitleA, 0, 1
7346, TitleAC, 2344, 3
3574, TitleAB, 2344, 2
2256, TitleAA, 2344, 1
1435, TitleC, 0, 1
4354, TitleCA, 1435, 1

I'm searching for a query that can order and output these rows like this:

TitleA
  TitleAA
  TitleAB
  TitleAC
TitleB
  TitleBA
  TitleBB
TitleC
  TitleCA

So the rows should be ordered by the first parent, then followed by its children items ordered. Then the next parent followed followed by its children items and so on.

The ordering of the parents themselves does not matter! The titles are only for better understanding and cannot be used for sorting. The important thing is that the children get sorted under their parents.

This is the last query i did:

SELECT *
FROM table t1
     LEFT JOIN table t2
     ON t2.id = t1.parent 
ORDER BY COALESCE(t1.ordering, t2.ordering), t2.ordering

Upvotes: 0

Views: 833

Answers (2)

Spectorsky
Spectorsky

Reputation: 667

Partial solution might be the following query:

SELECT t.title FROM t GROUP BY IF(parent=0,id,parent), 
IF(parent=0,-1,ordering), t.title ORDER BY IF([parent]=0,
id,parent), IF(parent=0,-1,ordering);

Result is like following:

TitleC
TitleCC
TitleA
TitleAA
TitleAB
TitleAC
TitleB
TitleBA
TitleBB

To indent the header of each group, something like report in MS Access might be used. I've prepared it in MS Access, so there may be difference slightly in MySQL syntax.

Upvotes: 2

sxn
sxn

Reputation: 167

SELECT *, if(parent = 0, id, parent) as main_order
FROM `tbl`
ORDER by main_order, ordering, parent

I think this query correctly work for 1 - parent child.

Solution the same problem see here: order sql tree hierarchy

Upvotes: 1

Related Questions