Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Location
    Hyderabd
    Posts
    14

    Need sql query to sort mysql results on multiple fields

    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.
    Last edited by vibhavram; 02-05-13 at 03:42. Reason: typographic mistakes found

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,475
    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.
    Last edited by tonkuma; 02-09-13 at 14:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •