CODEWITHSUNDEEP
mysqlsqlsql-order-by
Werner
Werner

Reputation: 1847

MySql: ORDER BY parent and child

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?

Upvotes: 24

Views: 17102

Answers (4)

Thomas Lobker
Thomas Lobker

Reputation: 446

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)

Upvotes: 4

Ndrezas
Ndrezas

Reputation: 31

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

Upvotes: 3

mxwltn
mxwltn

Reputation: 61

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

Upvotes: 6

lc.
lc.

Reputation: 116528

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)

Upvotes: 69

Related Questions