Search code examples
mysqlmysql-5.6

Multi Ordering 4 SQL columns with a single query

Environment: MySQL 5.6

SqlTable name = CategoryTable

Sql Columns

  • CATEGORY_ID (INT)
  • CATEGORY_NAME (VARCHAR)
  • LEVEL (INT)
  • MOTHER_CATEGORY (INT)

I've tried with

SELECT
    CATEGORY_ID, CATEGORY_NAME , LEVEL , MOTHER_CATEGORY
FROM
    CategoryTable

But I don't know how to use the ORDER BY in order to get that result.

So the first line here are the columns, and from the second lines, there start the table content:

CATEGORY_ID         CATEGORY_NAME            LEVEL     MOTHER_CATEGORY
     1              MainCategory               0               0
     2              -SubCategory1              1               1
     3              --SubCategory2             2               2
     4              ---SubCategory3            3               3
     5              2Nd_Main_Category          0               0
     6              -SubCategory1              1               5
     7              --SubCategory2             2               6
     8              ---SubCategory3            3               7

is there a way to achieve something like this with a mysql query?

Solution

  • You aren't very clear in what you are trying to achieve. I'll take a guess that you want to order using a multi-level parent child structure. there are some very complicated ways of handling such a feat within mysql 5.6, a DB that's not really ideal for such a structure, but I have come up with something simple myself that I use in my own apps. you create a special ordering field that creates a path of zero filled ids for each record.

    ordering_path_field
/
/0000000001/
/
/0000000001/0000000002
/0000000003
/0000000003/0000000005
/0000000003/0000000005/0000000006

    etc

    so each record contains a path of each parent up to the root, using zero filled ids. then you can just sort by this field to get them in proper order. the drawbacks being that you'll have to set a max number of levels allowed, so that the ordering fields doesn't overflow, and also, moving a record to a new parent if ever needed would be a big pain.