-3

I could solve this on php side, but I would like to use only one query:

I have this rows that represents categories and there list order and subcategory pathway:

 id   order pathway
  1     7     1
  2     5     2
  3     9     2/3
  4     0     4
  5     3     2/3/5

From that data I need to sort them primary by order number and then pathway to list subcategories inside there catetegory:

 id   order pathway
  4     0     4
  2     5     2
  3     9     2/3  
  5     3     2/3/5
  1     7     1

UPDATE: simple sort by order, pathway doesn't work in this case.

First row that need to be outputed is ID:4 because it has the lowest order number Then ID:2 because it has second lowest order number if we exclude ID:5 because it's inside categories 3 that is inside cat 2.

I also have column that represent category level (is it a main category or category inside some category and have deep) and column for category parent_id

Gale
  • 406
  • 13
  • 26
  • Do you have any ideas about SQL? And, specifically, about `ORDER BY` clause? – Aleks G Sep 10 '13 at 13:00
  • Possible duplicate of http://stackoverflow.com/questions/4876100/mysql-query-order-by-multiple-items – PM 77-1 Sep 10 '13 at 13:03
  • I don't see any discernable order by ascending/descending pattern in your expected output. MySQL (and most all RDBMS) ORDER BY clause is a fairly simple beast. If you need more advanced behaviour out of it, you're going to have to implement the sorting code yourself. – Jim Rubenstein Sep 10 '13 at 13:03

1 Answers1

3
SELECT * FROM yourtable ORDER BY order, pathway
makim
  • 3,154
  • 4
  • 30
  • 49
  • 2
    One can reverse the order by appending DESC (descending) resp. ASC: `ORDER BY "order" ASC, pathway DESC`. The word `order` is a keyword; better rename. – Joop Eggen Sep 10 '13 at 13:04
  • That doesn't sort subcategories inside there categories – Gale Sep 10 '13 at 13:07
  • I don´t get your tablestructure, categories are the first number of pathway and anything that followes are subcategories? – makim Sep 10 '13 at 13:09
  • @sine, yes, I just updated original question – Gale Sep 10 '13 at 13:12
  • @Joop Eggen this is example data, it's not real table structure – Gale Sep 10 '13 at 13:13
  • 1
    oooh so you have hierarchical Data, I´m no MySQL Guru but afaik mysql doesn´t support CTE´s so you can´t do this with a single SQL-Statement stick with your PHP Solution ;-) – makim Sep 10 '13 at 13:20