MySQL Order By Fields & Parents

Question

So I have a table with 7 items. Lets call them: Car, Boat, House, Bike, Wheels, Sink, Bed. We can simply call the table Table

Each with the following assigned numbers:

(ID)  (Item)    (#)  (parent_id)
1234 - Car    -  1 -  null
0000 - Boat   -  2 -  null
2222 - House  -  4 -  null
6545 - Bike   -  5 -  null
6547 - Wheels -  0 -  1234
4442 - Bed    -  1 -  2222
1474 - Sink   -  0 -  2222

Wheels is a child of car (or bike, doesnt matter) and sink & bed are children of House. The parent information is stored in the column called parent_id. It is null for everything that doesn't have a parent.

I need to use MySQL to sort this table via the assigned number order (called ordinal, the # in my tables here), however, retaining the parent-child order information. So the list should be sorted as follows:

(ID)  (Item)    (#)  (parent_id)
1234 - Car    -  1 -  null
6547 - Wheels -  0 -  1234
0000 - Boat   -  2 -  null
2222 - House  -  4 -  null
1474 - Sink   -  0 -  2222
4442 - Bed    -  1 -  2222
6545 - Bike   -  5 -  null

How can I do this with mysql? We can assume all this information is on one table.

`Is this possible to do using mysql?` yes, it is possible in mysql sql's dialect — Iłya Bursov, Apr 15 '15 at 16:16
you can start by writing query with simple sorting, then make it more complex — Iłya Bursov, Apr 15 '15 at 16:29

Alex · Accepted Answer · 2015-04-15T19:00:18.583

1

You should better keep parent_id but not the parent name.

Here is quick solution to order your table http://sqlfiddle.com/#!9/2a1fb/3

SELECT *
FROM table1
ORDER BY 
   CASE WHEN parent_id IS NULL THEN CAST(ID AS CHAR)
   ELSE CONCAT(CAST(parent_id AS CHAR),'-', CAST(ID AS CHAR)) END

EDIT 1 Variant #2 :-) http://sqlfiddle.com/#!9/76dcb/23

SELECT t1.* 
FROM table1 t1
LEFT JOIN table1 t2
ON t2.ID = t1.parent_id
ORDER BY 
   CASE WHEN t2.ord_idx IS NULL THEN CAST(t1.ord_idx AS CHAR)
   ELSE CONCAT(CAST(t2.ord_idx AS CHAR),'-',CAST(t1.ord_idx AS CHAR)) END

EDIT 2 to see how this order works you can just add this field to select part like:

SELECT t1.*, CASE WHEN t2.ord_idx IS NULL THEN CAST(t1.ord_idx AS CHAR)
   ELSE CONCAT(CAST(t2.ord_idx AS CHAR),'-',CAST(t1.ord_idx AS CHAR)) END as my_order 
FROM table1 t1
LEFT JOIN table1 t2
ON t2.ID = t1.parent_id
ORDER BY 
   CASE WHEN t2.ord_idx IS NULL THEN CAST(t1.ord_idx AS CHAR)
   ELSE CONCAT(CAST(t2.ord_idx AS CHAR),'-',CAST(t1.ord_idx AS CHAR)) END

edited Apr 15 '15 at 19:00

answered Apr 15 '15 at 16:30

Alex

16,739
1
28
51

Simple and elegant. Very nice. – zedfoxus Apr 15 '15 at 16:40
hmm i think this one might work but my actual database is different than what I had written here. The parent_id's are actually values correlating to the id of the actual item. I will update the table to clarify – user3669901 Apr 15 '15 at 16:43
This query doesnt take `ordinal` into consideration. What happens when you have 3 items in a tree. Car has wheels have hubcaps. – crthompson Apr 15 '15 at 16:46
I updated the list. Child items can not have children. The ONLY possibility is 1 parent item, but unlimited children for that parent. If hubcaps existed, they would be a child of car, as sink and bed are children of house. – user3669901 Apr 15 '15 at 16:47
perfect, thank you! I knew it was something with re-joining the table combining the parent_id to the id, but i wasnt certain on the order by part. would you mind explaining what exactly is going on in the order by part? Particularly the cast and concat parts? what does "cast as char" do exactly? – user3669901 Apr 15 '15 at 18:57
CAST just converts integer to string type – Alex Apr 15 '15 at 18:58
Ok, thats what I figured. So, assuming the "ints" are strings already, we can ignore the cast parts entirely, correct? So then we would be able to simply change this to when t2.ord_idx is null then t1.ord_idx else concat(t2.ord_idx),'-',t1.ord_idx) end ? – user3669901 Apr 15 '15 at 19:07
yes. that is just type dependend. CONCAT should get strings as parameters – Alex Apr 15 '15 at 19:09
so then how to add ordering direction? 'desc or asc' – Mansour Alnasser Jul 30 '19 at 08:07
@alnassre so just add it to `ORDER BY ...` expression. It is there, just by default it is `ASC`. In my examples if you want to change direction you need to add it at the end after `END`. Like `ORDER BY CASE WHEN ... THEN ... ELSE ... END DESC ` – Alex Jul 30 '19 at 14:46

score 0 · Answer 2 · answered Apr 15 '15 at 16:28

Something like that should work. I assumed the following field id, (#) = order_num, and parent_id:

SELECT * FROM table as t
ORDER BY
   CASE
       WHEN parent_id IS NOT NULL THEN (SELECT id FROM table WHERE id = t.parent_id LIMIT 1)
       ELSE id
   END,
   CASE
       WHEN parent_id IS NULL THEN -1
       ELSE order_num
   END;

score 0 · Answer 3 · answered Apr 15 '15 at 16:36

Assuming the following structure:

CREATE TABLE `Table` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`number` INT NOT NULL,
`parent_id` VARCHAR(32) DEFAULT NULL,
PRIMARY KEY(`id`)
);

something like this:

SELECT `Table`.* FROM `Table`
LEFT JOIN `Table` dependant ON dependant.`name` = `Table`.`parent_id`
ORDER BY CONCAT(IF(dependant.`name` IS NULL,`Table`.`name`,CONCAT(dependant.`name`,':',`Table`.`name`))) ;

may work.

NOTES

When asking a question about RDBMS consider creating initial table structure and data SQL. There would be many more willing to answer then
Read this article on how to store hierarchy data in MySQL. It used to host on .mysql.com, but for some reason they removed it. It's a wonderful arrticle.
Consider moving to nested sets

I have no control over the database. I can only access it. – user3669901 Apr 15 '15 at 17:01 — user3669901, Apr 15 '15 at 17:01

MySQL Order By Fields & Parents

3 Answers3

Linked