# Mysql – How to sort category by path and sort order

MySQL

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

When I run query

SELECT * FROM category order by path;


RESULT:

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


EDIT :

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

RESULT:

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
FROM (
SELECT id, parent_id, label, levelx, path, sort_order
, substr(path,2,1) FirstLevel
FROM t1 WHERE Label IS NOT NULL
)
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 Label
FROM T1 a
JOIN
(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)