23

I have a table like:

+------+---------+-
| id   | parent  |
+------+---------+
| 2043 |    NULL |
| 2044 |    2043 |
| 2045 |    2043 |
| 2049 |    2043 |
| 2047 |    NULL |
| 2048 |    2047 |
| 2049 |    2047 |
+------+---------+

which shows a simple, 2-level "parent-child"-corelation. How can I ORDER BY an SELECT-statement to get the order like in the list above, which means: 1st parent, childs of 1st parent, 2nd parent, childs of 2nd parent and so on (if I have that, I can add the ORDER BYs for the children... I hope). Is it possible withoug adding a sort-field?

Werner
  • 1,695
  • 3
  • 21
  • 42

4 Answers4

67

Including sorting children by id:

ORDER BY COALESCE(parent, id), parent IS NOT NULL, id

SQL Fiddle example

Explanation:

  • COALESCE(parent, id): First sort by (effectively grouping together) the parent's id.
  • parent IS NOT NULL: Put the parent row on top of the group
  • id: Finally sort all the children (same parent, and parent is not null)
lc.
  • 113,939
  • 20
  • 158
  • 187
  • That’s a problem on sqlfiddle’s end it seems. Try this one though http://www.sqlfiddle.com/#!9/63640/1 – lc. Oct 24 '19 at 08:32
6

If your table uses 0 instead of null to indicate an entry with no parent:

id   | parent
-------------
1233 | 0
1234 | 1233
1235 | 0
1236 | 1233
1237 | 1235

Use greatest instead of coalesce and check the value does not equal 0:

ORDER BY GREATEST(parent, id), parent != 0, id
mxwltn
  • 61
  • 1
  • 2
  • 1
    this didn't work, I had to use this: ORDER BY COALESCE(IF(parent_id=0,NULL,parent_id), id), parent_id IS NOT NULL, id; – Abeer Sul Aug 17 '20 at 18:58
4

This question still shows as one of the first search results. So I would like to share a my solution and hope it will help more people out. This will also work when you have a table with many levels of parent and child relations. Although it is quite a slow solution. The top level has NULL as parent.

+---------+---------+
| id      | parent  |
+---------+---------+
| 1       | NULL    |
| 2       | 1       |
| 3       | 1       |
| 4       | 2       |
+---------+---------+

In my approach I will use a procedure that will recursively call itself and keep prepending the path with the parent of the requested id until it reaches the NULL parent.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `PATH`(IN `input` INT, OUT `output` VARCHAR(128))
BEGIN

  DECLARE _id INT;
  DECLARE _parent INT;
  DECLARE _path VARCHAR(128);

  SET `max_sp_recursion_depth` = 50;

  SELECT `id`, `parent`
  INTO _id, _parent
  FROM `database`.`table`
  WHERE `table`.`id` = `input`;

  IF _parent IS NULL THEN
    SET _path = _id;
  ELSE
    CALL `PATH`(_parent, _path);
    SELECT CONCAT(_path, '-', _id) INTO _path;
  END IF;

  SELECT _path INTO `output`;

END $$
DELIMITER ;

To use the results in an ORDER BY clause you will need a FUNCTION too that wraps the results of the PROCEDURE.

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `GETPATH`(`input` INT) RETURNS VARCHAR(128)
BEGIN

  CALL `PATH`(`input`, @path);
  RETURN @path;

END $$
DELIMITER ;

Now we can use the recursive path to sort the order of the table. On a table with 10000 rows it takes just over a second on my workstation.

SELECT `id`, `parent`, GETPATH(`id`) `path` FROM `database`.`table` ORDER BY `GETPATH`(`id`);

Example output:

+---------+---------+---------------+
| id      | parent  | path          |
+---------+---------+---------------+
| 1       | NULL    | 1             |
| 10      | 1       | 1-10          |
| 300     | 10      | 1-10-300      |
| 301     | 300     | 1-10-300-301  |
| 302     | 300     | 1-10-300-302  |
+---------+---------+---------------+
5 rows in set (1,39 sec)
Thomas Lobker
  • 436
  • 4
  • 5
  • after re-reading the initial question I see it was just asking about 2-level descendants where a single parent may have multiple descendants (siblings). This answer goes above and beyond and covers the case of N-level descendants which is what I needed – chiliNUT Nov 10 '21 at 07:31
  • thankyou! it solve the problem for N-level of tree – NoobnSad Sep 06 '22 at 03:37
  • It´s work nice, but I have one sugestion, the order in path is not ok because 1-10 is greater than 1-2 in numerical order, but not in text order, so I would change the line 'SELECT CONCAT(_path, '-', _id) INTO _path' adding a LPAD, like 'SELECT CONCAT(_path, '-', LPAD(_id,3,0)) INTO _path;. – SauloAlessandre Mar 23 '23 at 22:16
3

The solution above didn't work for me, my table used 0 instead of NULL. I found this other solution: you create a column with the concatened parent id and child id in your query and you can sort the result by it .

SELECT CONCAT(IF(parent = 0,'',CONCAT('/',parent)),'/',id) AS gen_order
FROM table 
ORDER BY gen_order
Ndrezas
  • 31
  • 2