CODEWITHSUNDEEP
mysqlsql
Maulik patel
Maulik patel

Reputation: 1601

issue in order by query

i have below table:-

id name parent_id
1  ABC  0
2  XYZ  0
3  BB   1
4  AA   1
5  PQR  0

i want's such order by query which display such results.

1  ABC  0
4  AA   1
3  BB   1
5  PQR  0
2  XYZ  0

i.e parent and child project in ascending order and parent project name followed by child projects.

i use such query but when we use order by level then(without ascending) ok but i wants order level with name.

SELECT distinct `pp088_projects`.* , ( 
    SELECT LPAD(parent.id, 5, '0')
      FROM `pp088_projects` parent
      WHERE parent.id = `pp088_projects`.id 
      AND parent.parent_id = 0 
    UNION 
    SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0'))
      FROM `pp088_projects` parent 
      INNER JOIN `pp088_projects` child
      ON (parent.id = child.parent_id)
      WHERE child.id = `pp088_projects`.id 
      AND parent.parent_id = 0
    UNION 
    SELECT CONCAT(LPAD(parent.id, 5, '0'), '.', LPAD(child.id, 5, '0'), '.', 
            LPAD(grandchild.id, 5, '0')) 
      FROM `pp088_projects` parent
      INNER JOIN `pp088_projects` child ON (parent.id = child.parent_id) 
      INNER JOIN `pp088_projects` grandchild ON (child.id = grandchild.parent_id) 
      WHERE grandchild.id = `pp088_projects`.id AND parent.parent_id = 0 ) AS level
  FROM `pp088_projects` , `pp088_project_users`
  WHERE (`pp088_projects`.`id` = `pp088_project_users`.`project_id` 
  AND `pp088_project_users`.`user_id` = '1')
  AND (`pp088_projects`.`completed_on` = '0000-00-00 00:00:00')
  ORDER BY level

Upvotes: 0

Views: 91

Answers (1)

eggyal
eggyal

Reputation: 126035

Since you only have two levels of depth, recursion (for which MySQL has limited support) is not required; instead to simply order by id:

SELECT   *
FROM     pp088_projects
ORDER BY IF(parent_id, parent_id, id), parent_id, id

See it on sqlfiddle.

Or, if you want to order by name:

SELECT      child.*
FROM        pp088_projects child
  LEFT JOIN pp088_projects parent ON child.parent_id = parent.id
ORDER BY    COALESCE(parent.name, child.name), parent.id, child.name

See it on sqlfiddle.

Upvotes: 1

Related Questions