first of all I am sory for my english.

My category structure is shown below. My purpose is to get category structure with single query. I sort my table by path and I get category tree.

sort_order : Indicates sort order between categories with same level

enter image description here

When I run query

SELECT * FROM category order by path;


enter image description here

But result must be as shown below because sort_order of Electronic is 1 and this is less than sort_order value of Computer category 2

 - ELectronic 
 - - TV 
 - - - LCD 
 - - - - LED LCD 
 - Computer 
 - - Laptop


For @adopilot

Query : SELECT * FROM category order by level, path;


enter image description here

  • did you try order by level,path ? You can put more then one column in order
    – adopilot
    Feb 20, 2013 at 13:50
  • yes I tried it but it doesn't work as ı need :( Feb 20, 2013 at 14:02
  • @adopilot I have added result of your query to question Feb 20, 2013 at 14:07
  • Which DBMS are you using? Oracle? Postgres?
    – user1822
    Feb 20, 2013 at 14:11
  • I am using MySql Feb 20, 2013 at 14:12

2 Answers 2


The problem is you need to extract the sort order for the root items and make that same order apply to the sub-items in the tree without re-ordering the sub-items. In Oracle you can do this with a windowing function as follows:

SELECT RPAD('- ',Length(Path)-1,'- ') || label, levelx, path, sort_order
   , min(sort_order) OVER (PARTITION BY FirstLevel) Sort_Order2
   SELECT id, parent_id, label, levelx, path, sort_order
      , substr(path,2,1) FirstLevel 
ORDER BY Sort_Order2, Path;

I don't know if you can do something like that in MYSQL, so here is a version using a GROUP BY and self join that works in MySQL 5.5.28.

   (SELECT SUBSTR(Path,2,1) FirstLevel, MIN(Sort_Order) FirstSort FROM T1 
      WHERE Label IS NOT NULL GROUP BY SUBSTR(Path,2,1)) b
   ON substr(a.Path,2,1) = b.FirstLevel
ORDER BY FirstSort, Path;

(SQL Fiddle)

  • What an imagination. You got the ordering. Indentation is just a formality from here. +1 !!! Feb 22, 2013 at 17:12
  • I did the ordering on the Oracle version, but didn't know the MySQL syntax to do it for that version. Feb 22, 2013 at 18:29

It appears that using Sort Order in any combination just to accommodate a particular level is just not going to cut it in this instance. I believe what you are looking for is an in-order tree traversal.

I have already written posts on how to do pre-order tree traversal

You should be able to pull this off using id,parent_id,sort_order and path.

In fact, let me describe a pseudo-algorithm.

    • Push (Place New Data on Top of Stack)
    • Pop (Remove Data From Top of Stack)
  • ALGORITHM START : Push top parent_id (0)
  • LOOP
    • Pop parent_id from the Stack
    • Display label of parent_id indented based on level
    • Retrieve All id values from parent_id whose status = 1
    • Push All Retrieved id values by sort_order DESC, path DESC into the Stack
  • END LOOP when Stack is Empty after Pop with nothing to Push

I will leave it to you write the Stored Procedures for handling a Stack.

  • Thank you for answer. I will try to adopt your examples to my problem. Feb 21, 2013 at 6:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.