MySQL Sorting by parents and children

Question

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

The titles were chosen by me to better understand the example. Of course you can not do an alphabetically sort in real life. That's should be clear. — Mike, Sep 03 '18 at 17:27
Should we just assume what else is chosen by you? How do you know that TitleA should be in front of the TitleB? — sskoko, Sep 03 '18 at 17:33
do you see the last column called ordering and the example output i posted? — Mike, Sep 03 '18 at 17:37
You changed your answer and updated the id's but the parent field is now pointing to old id's. This just can't work! — sskoko, Sep 03 '18 at 18:34
It's still the same. I changed the IDs so people don't try a solution by sorting IDs. — Mike, Sep 03 '18 at 18:36

Spectorsky · Accepted Answer · 2018-09-03T22:02:39.367

2

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.

edited Sep 03 '18 at 22:02

answered Sep 03 '18 at 18:31

Spectorsky

608
4
23

Thanks, but the titles were only chosen for better understanding the example. They can totally vary and can't be used for sorting. – Mike Sep 03 '18 at 18:33
Without title, how do we know who is whose parent and child? `TitleA`, `TitleB`, `TitleC` have the same `parent` value =0, and the same `ordering`=1. – Spectorsky Sep 03 '18 at 18:35
look at the id and the parent columns. – Mike Sep 03 '18 at 18:38
The ordering of the parents doesn't matter. the important thing is that the children get sorted under their parents. – Mike Sep 03 '18 at 18:40
Has `id` some info about parent-child relation? – Spectorsky Sep 03 '18 at 18:42
Of course. You see that the ID 2344 has three childs. – Mike Sep 03 '18 at 18:43
Second digit? `TitleB` is Ok with 2 children. But why `TitleC` has `id=1435` with only 1 child? – Spectorsky Sep 03 '18 at 18:47
correct. like you see it in the example output i posted. – Mike Sep 03 '18 at 18:49
`TitleC` is not correct, because its `id=1435` has the second digit=4, but it has only 1 child. – Spectorsky Sep 03 '18 at 18:57
It's fine. Second digit if the id is irrelevant – Mike Sep 03 '18 at 19:36
I have to say that I cannot understand why `id` defines the number of children, and how to determine these children for each parent without `title`. `parent` and `ordering` don't allow to distinguish `TitleA`, `TitleB` and `TitleC`. – Spectorsky Sep 03 '18 at 20:06
The ID does not define the number of the children. The parent defines the belonging and in such the number of children. – Mike Sep 03 '18 at 20:18
`TitleA`, `TitleB` and `TitleC` have the.same parent=0. How can we determ their children? – Spectorsky Sep 03 '18 at 20:37
I see. `parent` of child refer to `id of its parent. Sorry for being woody. Start to think about the task. – Spectorsky Sep 03 '18 at 20:40
What is "IIf"? Does not look like MySQL. – Mike Sep 03 '18 at 21:39
In some implementaton it is called `if`. In MySQL it is `IF`. [See manual for detail](https://dev.mysql.com/doc/refman/8.0/en/control-flow-functions.html) – Spectorsky Sep 03 '18 at 21:53
Square Brackets are also not valid in MySQL – Mike Sep 03 '18 at 21:55
Sorry, I've prepared it in MS Access. Square brackets stands for reference to table field. Just omit it. – Spectorsky Sep 03 '18 at 21:57
This actually works...! It does exactly what i was searching for. The query looks really creepy. I thought this could be done in some cleaner way. – Mike Sep 04 '18 at 11:03

sxn · Answer 2 · 2018-09-03T18:24:30.400

1

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

edited Sep 03 '18 at 18:24

answered Sep 03 '18 at 18:04

sxn

157
1
7

looks interesting, but the ordering is wrong. IDs should not be a search criteria. – Mike Sep 03 '18 at 18:10
what about this solution https://stackoverflow.com/questions/14890204/order-sql-tree-hierarchy – sxn Sep 03 '18 at 18:21
Not really. Nested Tree Sets are for unlimited hierarchies and i cant create functions. – Mike Sep 03 '18 at 18:24
This solution work slowly when insert and update But work fast when select – sxn Sep 03 '18 at 18:26

MySQL Sorting by parents and children

2 Answers2