mysql sort category according to parent id

Question

This is the table structure

id   parent_id   name
1    0           BMW
2    0           Mercedez
3    0           Porsche
4    1           3 Series
5    2           E60
6    1           5 Series
7    3           Cayenne

How can i show the table as

BMW
3 Series
5 Series
Mercedez
E60
Porsche
Cayenne 

The above table shows ascending id followed by parent_id associated with that id, then go to second id and so on. I need in a single query, is it possible to do as such?

Answer 1

Try this:

SELECT
  name,
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END AS Sort
FROM
  cars
ORDER BY
  Sort,
  id

http://sqlfiddle.com/#!2/9b05f/3

---

EDIT:

Given that this answer keeps getting upvotes, I revisited the question and found a flaw. If, for some reason, the parent has a higher ID than the child, the ordering gets messed up. The above query only works if the parent ID is a lower number than all the children.

To demonstrate the problem, imagine the table looked like this:

id   parent_id   name
8    0           BMW
2    0           Mercedez
3    0           Porsche
4    8           3 Series
5    2           E60
6    8           5 Series
7    3           Cayenne

Notice now that BMW has an id of 8 instead of 1. The result will look like this:

Mercedez
E60
Porsche
Cayenne
3 Series
5 Series
BMW

Notice above that BMW shows up at the bottom of the list, after its children! This is because the secondary sorting orders by id, and if the parent ID happens to be higher than any children, the parent may not show up on top of the children.

This query will solve that problem:

SELECT
  name
FROM
  cars
ORDER BY
  CASE WHEN parent_id = 0 THEN id ELSE parent_id END, -- another way of writing it: IFNULL(NULLIF(parent_id, 0), id)
  parent_id,
  id

http://sqlfiddle.com/#!2/6d6d73/3

To explain what's going on here, you first order by the parent row's id field and the children rows' parent_id field. If you order by that, all the children will be grouped up with their parent, and the overall list will be ordered by the parent's id field.

But that doesn't set the sorting within the families, so the parent could show up anywhere within the family (the parent could show up at the top, or it could be in the middle, or it could be last).

That's where the other two sorting fields come in. The second field sorts by parent_id, and the parent row's parent_id field is always 0. Safely assuming that your ID fields are always positive, this means that the parent record will always show up on top within the family. The rest of the children will all have the same value for parent_id, so the third sorting field orders the children within the family by their id field. This could also be changed to name, depending on how you want the children sorted.