Ordering MySql Based on Multiple Columns

Question

I'm trying to sort a mysql table by a few different columns. Here's the table:

+------------+------------+------------+------------+
|     id     |  parent_id |  ordering  |    level   |
+------------+------------+------------+------------+
|      1     |     0      |      0     |      0     |
+------------+------------+------------+------------+
|      2     |     0      |      2     |      0     |
+------------+------------+------------+------------+
|      3     |     0      |      3     |      0     |
+------------+------------+------------+------------+
|      4     |     0      |      4     |      0     |
+------------+------------+------------+------------+
|      5     |     2      |      0     |      1     |
+------------+------------+------------+------------+
|      6     |     0      |      1     |      0     |
+------------+------------+------------+------------+

And this is the result I'd like to achieve:

+------------+------------+------------+------------+
|     id     |  parent_id |  ordering  |    level   |
+------------+------------+------------+------------+
|      1     |     0      |      0     |      0     |
+------------+------------+------------+------------+
|      6     |     0      |      1     |      0     |
+------------+------------+------------+------------+
|      2     |     0      |      2     |      0     |
+------------+------------+------------+------------+
|      5     |     2      |      0     |      1     |
+------------+------------+------------+------------+
|      3     |     0      |      3     |      0     |
+------------+------------+------------+------------+
|      4     |     0      |      4     |      0     |
+------------+------------+------------+------------+

As you can (hopefully) see, I would like to sort the items by ordering and parent_id. I would prefer, if possible, to use a single query. If this is not possible, I can use a PHP loop in combination with the query. I'd like to stay away from multiple queries if at all possible.

I need a way to sort so that a row with a parent_id such as row with id=5 is below it's "parent row" (row with id=2). 'ordering' is based on 'level'.

The closest I've come is by sorting this with:

SELECT * FROM table ORDER BY ordering,id,parent_id

Which returns:

+------------+------------+------------+------------+
|     id     |  parent_id |  ordering  |    level   |
+------------+------------+------------+------------+
|      1     |     0      |      0     |      0     |
+------------+------------+------------+------------+
|      5     |     2      |      0     |      1     |
+------------+------------+------------+------------+
|      6     |     0      |      1     |      0     |
+------------+------------+------------+------------+
|      2     |     0      |      2     |      0     |
+------------+------------+------------+------------+
|      3     |     0      |      3     |      0     |
+------------+------------+------------+------------+
|      4     |     0      |      4     |      0     |
+------------+------------+------------+------------+

I honestly don't understand how you consider the result sorted. Neither the values in ordering nor parent_id are in a sorted order. — user149341
– user149341, Commented Mar 16, 2013 at 3:12
Can you elaborate -- I don't see how ordering and parent_id are sorted at all... — sgeddes
– sgeddes, Commented Mar 16, 2013 at 3:13
If the rows were ordered 1, 6, 2, 3, 4, 5, then I would understand what you are asking. — scott.korin
– scott.korin, Commented Mar 16, 2013 at 3:15
Please add some more detail. Describe what you have tried and what your results were. This will help the SO community to assist you. — Jeff Wolski
– Jeff Wolski, Commented Mar 16, 2013 at 3:40

sgeddes · Accepted Answer · 2013-03-16 03:15:00Z

3

Are you trying to sort by the SUM of the Ordering and Parent_Id columns? Assuming those were both numeric data types, this should work:

SELECT *
FROM YourTable
ORDER BY Parent_Id + Ordering, Id

answered Mar 16, 2013 at 3:15

sgeddes

62.9k7 gold badges68 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

JohnFx Over a year ago

Wow, an upvote just for reverse engineering that puzzle of a question.

sgeddes Over a year ago

@JohnFx -- lmao -- That's the only "ordering" I see in the example above :D

ro ko Over a year ago

That was relatively a fast answer, owing to the fact most of the people didn't understand the question :) Your answer does look as if it satisfies his requirement though.

RavatSinh Sisodiya · Accepted Answer · 2013-03-16 06:28:00Z

1

SQLFiddle Demo

SELECT * FROM mytable ORDER BY parent_id + ordering, id

edited Mar 16, 2013 at 6:28

answered Mar 16, 2013 at 3:23

RavatSinh Sisodiya

1,6161 gold badge21 silver badges42 bronze badges

Collectives™ on Stack Overflow

Ordering MySql Based on Multiple Columns

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related