1

I have this MySql table (default ordered by id):

id subid value
1  0     value 1
2  0     value 2
3  1     sub of 1
4  1     sub of 1
5  2     sub of 2
6  2     sub of 2
7  5     sub of 5

I need a query that returns the list, ordered by the id AND the subid like this:

1  0     value 1
3  1     sub of 1
4  1     sub of 1
2  0     value 2
5  2     sub of 2
7  5     sub of 5
6  2     sub of 2

So, the subs of an id come directly under that id line (in this example id1 has 2 subs and they appear directly under the line with id1. id7 has subid 5 so appears directly under the line with id5

subs can be infinitely deep

How can I get this result?

Thank you!

John Wings
  • 11
  • 3
  • 2
    Have you tried like this ?`ORDER BY ID,SUBID` – Jaydip Jadhav Aug 28 '17 at 12:29
  • Yes, tried it, it returns the same list as the original :-( – John Wings Aug 28 '17 at 12:33
  • 1
    a GOOGLE search on sorting a hierachy for mySQL returned: https://stackoverflow.com/questions/14890204/order-sql-tree-hierarchy and https://stackoverflow.com/questions/15852045/mysql-sorting-hierarchical-data and https://stackoverflow.com/questions/10000643/mysql-hierarchical-grouping-sort most of which seem viable. – xQbert Aug 28 '17 at 12:39
  • After close inspection of the 2nd and 3rd options I don't think they would work. The first option seems to be the most promising; but requires structure changes. The only way I can see doing this is using a recursive function passing out the path to the next level until a full path is defined. But I would think that would be expensive; so storing the path seems like the most efficient solution. – xQbert Aug 28 '17 at 13:05

2 Answers2

0

Try some thing like this :

SELECT ID,SUBID,Value
FROM TableName
ORDER BY RIGHT(Value,CHAR_LENGTH(SUBID)),ID
Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
  • ID is unique. Sorting by it first will result in the original order. and not the `1,3,4, 2`... order desired. – xQbert Aug 28 '17 at 12:43
  • May be what your are saying is correct. but let op try this first. :P – Jaydip Jadhav Aug 28 '17 at 12:45
  • Ok; but here's a sample showing it doesn't produce the desired results: http://rextester.com/NYZCK71234 – xQbert Aug 28 '17 at 12:52
  • @xQbert Updated the order of column in ORDER By clause. you can check it now. – Jaydip Jadhav Aug 29 '17 at 04:28
  • Closer; but ID's 6/7 are in wrong order. http://rextester.com/ZDXT30152 feel free to test it out yourself. The issue here is that "subs can be infinitely deep" Which means to get this you need to recursively loop though all the records for each record and keep a "path" to the ancestor. to do this we would also have to pad or somehow keep track of each set so the numbers could be something like 00001.00015.00087.000018 for 4 levels deep; with the top ancestor being listed first each time. This way the string compare is consistent. This is a non-trivial effort in mySQL. – xQbert Aug 29 '17 at 12:53
0

Thank you for answering people, It seems to be quite difficult to return the data like that.
Allow me to give you more info about my goal:
I'm building a project in Laravel, and want to show the data in that order because I'm using the jQuery addon jQueryTreetable (http://ludo.cubicphuse.nl/jquery-treetable/) and on their site you can read this remark:

Please note that the plugin expects the rows in the HTML table to be in the same order in which they should be displayed in the tree. For example, suppose you have three nodes: A, B (child of node A) and C (child of node B). If you create rows for these nodes in your HTML table in the following order A - C - B, then the tree will not display correctly. You have to make sure that the rows are in the order A - B - C.

Maybe a solution can be to get the data into JSON format, and let javascript change the order of the data?

Thank you!

John Wings
  • 11
  • 3