Need sql query to sort mysql results on multiple fields

vibhavram · #1 (**permalink**) 02-05-13, 03:23

I need a sql query to sort the results from a sql table.

The actual structure of table is like

Table name : categories

catid parentid catname level

-----------------------------------------------------------

1 0 services 1

2 1 printing 2

3 1 cabling 2

4 0 Products 1

5 4 switches 2

6 4 cables 2

7 1 lubricating 2

Now I want to sort the above results grouped by parentid including parent category and then by catname in alphabetical order. Can some one suggest me the best way to do this using sql query.

I want the results to be sorted in the following format

4 0 Products 1
6 4 cables 2
5 4 switches 2
1 0 services 1
3 1 cabling 2
7 1 lubricating 2
2 1 printing 2

Products and services are level 1 categories and others are sub categories. Now I grouped subcategories along with parent category and sorted level 1 categories in alphabetical order and then sorted sub categories in alphabetical order.

tonkuma · #2 (**permalink**) 02-09-13, 14:18

From the provided sample data, max level might be two.
If this guess was right,
the following query might be an answer...

Code:

SELECT c.*
 FROM  categories AS c
 INNER JOIN
       categories AS p
  ON   p.catid = COALESCE( NULLIF(c.parentid , 0) , c.catid )
 ORDER BY
       p.catname
     , c.level
     , c.catname
;

If the max level was not clear,
loop or recursive query might be neccesary to find the catname of the level 1 categories.

Internet Services

Web Development

Digital Marketing

Consumer Tech