Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;

RESULT:

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

EDIT :

For @adopilot

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

RESULT:

enter image description here

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

2 Answers 2

up vote 3 down vote accepted

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)

share|improve this answer
    
What an imagination. You got the ordering. Indentation is just a formality from here. +1 !!! –  RolandoMySQLDBA Feb 22 '13 at 17:12
    
I did the ordering on the Oracle version, but didn't know the MySQL syntax to do it for that version. –  Leigh Riffel Feb 22 '13 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.

  • DATA STRUCTURE : Stack
  • STACK OPERATIONS
    • 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.

share|improve this answer
    
Thank you for answer. I will try to adopt your examples to my problem. –  Ferid Movsumov Feb 21 '13 at 6:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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