CODEWITHSUNDEEP
mysqlsql
Brad Proctor
Brad Proctor

Reputation: 563

SQL nested order by?

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

Upvotes: 6

Views: 8537

Answers (4)

davogotland
davogotland

Reputation: 2777

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;

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753625

Whenever possible, I build SQL incrementally, not least because it gives me the option of testing as I go.

The first thing we need to be able to do is identify the top-level categories:

 SELECT category_id   AS tl_cat_id,
        category_name AS tl_cat_name,
        display_order AS tl_disp_order
   FROM Categories
  WHERE parent_id = 0;

Now we need to join that with the categories and subcategories to get the result:

SELECT t.tl_cat_id, t.cat_name, t.tl_disp_order, c.category_id, c.category_name,
       CASE WHEN c.parent_id = 0 THEN 0 ELSE c.display_order END AS disp_order
  FROM Categories AS c
  JOIN (SELECT category_id   AS tl_cat_id,
               category_name AS tl_cat_name,
               display_order AS tl_disp_order
          FROM Categories
         WHERE parent_id = 0) AS t
     ON c.tl_cat_id = t.parent_id OR (c.parent_id = 0 AND t.tl_cat_id = c.category_id)
  ORDER BY tl_disp_order, disp_order;

The join condition is unusual but should work; it collects rows where the parent ID is the same as the current category ID, or rows where the parent ID is 0 but the category ID is the same. The ordering is then almost trivial - except that when you are dealing with the sub-category ordering, you want the parent item at the front of the list. The CASE expression handles that mapping.

Upvotes: 0

DRapp
DRapp

Reputation: 48139

Sounds almost identical to another I've answered with similar parent/child hierarchy while retaining child elements at same grouped level as its corresponding parent...Check this thread

Upvotes: 0

Amadan
Amadan

Reputation: 198324

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.

Upvotes: 8

Related Questions