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.

Answer 2

an answer has already been accepted, but i thought i would share my thoughts on this anyways. i tried to sort the main categories after their display_order column as well. here's my table

mysql> select * from categories;
+-------------+---------------+-----------+---------------+
| category_id | category_name | parent_id | display_order |
+-------------+---------------+-----------+---------------+
|           1 | B             |         0 |             2 |
|           2 | C             |         0 |             3 |
|           3 | b2            |         1 |             2 |
|           4 | b1            |         1 |             1 |
|           5 | c3            |         2 |             3 |
|           6 | A             |         0 |             1 |
|           7 | c2            |         2 |             2 |
|           8 | b3            |         1 |             3 |
|           9 | a2            |         6 |             2 |
|          10 | a1            |         6 |             1 |
|          11 | c1            |         2 |             1 |
|          12 | a3            |         6 |             3 |
+-------------+---------------+-----------+---------------+
12 rows in set (0.00 sec)

as you see, i have taken great care to add the categories in a none linear order :)

my query:

SELECT
    sub_id AS category_id,
    sub_name AS category_name,
    sub_parent_id AS parent_id,
    main_order + sub_order AS display_order
FROM (
    SELECT
        c1.display_order + c1.display_order * (
            SELECT
                inner_c.display_order
            FROM
                categories AS inner_c
            WHERE
                inner_c.parent_id <> 0
            ORDER BY
                inner_c.display_order DESC
            LIMIT 1) AS main_order,
        c2.display_order AS sub_order,
        c2.category_name AS sub_name,
        c2.category_id AS sub_id,
        c2.parent_id AS sub_parent_id
    FROM
        categories AS c1
    JOIN
        categories AS c2
    ON
        c1.category_id = c2.parent_id
    WHERE
        c1.parent_id = 0
    ) AS renumbered
UNION ALL
SELECT
    category_id,
    category_name,
    parent_id,
    display_order + display_order * (
        SELECT
            inner_c.display_order
        FROM
            categories AS inner_c
        WHERE
            inner_c.parent_id <> 0
        ORDER BY
            inner_c.display_order DESC
        LIMIT 1) AS display_order
FROM
    categories
WHERE
    parent_id = 0
ORDER BY
    display_order;