mysql - SQL nested order by?

Question

I'm sure that this has been asked before, but I don't know what to call it exactly to find the answer.

I have a table of categories and sub categories. They each have an id and a parent id. If it is a top level category, the parent id is 0. Sub categories have the parent id set to the category id of it's parent.

category_id          # The ID for this record
category_name        # The name of the category
parent_id            # The parent ID for this category
display_order        # Order of categories within their grouping

1 A  0 0     # First primary category
2 a1 1 0     # Subcategory, parent is A, display_order is 0
3 a2 1 1
4 a3 1 2

5 B  0 1     # Second primary category
6 b1 5 0     # Subcategory, parent is B, display_order is 0
7 b2 5 1
8 b3 5 2

I'm trying to write an SQL query that will give me all of the categories in this order:

A, a1, a2, a3, B, b1, b2, b3

SELECT * FROM categories ORDER BY display_order 

Is this possible in SQL, or will I need to use multiple queries

Thanks, Brad

Answer 1

Something like this might maybe work:

SELECT *
FROM categories
ORDER BY IF(parent_id, parent_id, category_id), parent_id, display_order

but since it can't use an index, it'll be slow. (Didn't test though, might be wrong)

The first ORDER BY condition sorts parents and children together; then the second one ensures the parent precedes its children; the third sorts the children among themselves.

Also, it will obviously work only in the case you directly described, where you have a two-level hierarchy.