MySQL Sorting by parents and children
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
you see the table above, right?
– Mike
Sep 3 at 17:24
If that's the case simple "ORDER BY title" will do.
– sskoko
Sep 3 at 17:25
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 3 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 3 at 17:33
2 Answers
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.
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 3 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 3 at 18:35
TitleA
TitleB
TitleC
parent
ordering
look at the id and the parent columns.
– Mike
Sep 3 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 3 at 18:40
Has
id some info about parent-child relation?– Spectorsky
Sep 3 at 18:42
id
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
looks interesting, but the ordering is wrong. IDs should not be a search criteria.
– Mike
Sep 3 at 18:10
what about this solution stackoverflow.com/questions/14890204/order-sql-tree-hierarchy
– sxn
Sep 3 at 18:21
Not really. Nested Tree Sets are for unlimited hierarchies and i cant create functions.
– Mike
Sep 3 at 18:24
This solution work slowly when insert and update But work fast when select
– sxn
Sep 3 at 18:26
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
How about you at least show us your tables?
– sskoko
Sep 3 at 17:23