mySQL hierarchical grouping sort

Go To


I have a schema that essentially looks like this:

  `id` int(10) unsigned NOT NULL,
  `title` text,
  `type` tinyint(4),
  `parent` int(10)

The type field is just an enum where 1 is a parent type, and 2 is a child type (in actuality there are many types, where some should behave like parents and some like children). The parent field indicates that a record is the child of another record.

I know this is probably not ideal for the query I want to build, but this is what I have to work with.

I would like to sort and group the data so that the parent records are sorted by title, and grouped under each parent is the child records sorted by title. Like so:

 ID | title       |type |parent 
 4  | ParentA     | 1   |
 2  | ChildA      | 2   | 4
 5  | ChildB      | 2   | 4
 7  | ParentB     | 1   |
 9  | ChildC      | 2   | 7
 1  | ChildD      | 2   | 7

** Edit **

We should be able to take the type field out of the picture entirely. If parent is not null then it should be grouped underneath it's parent.

2012-04-03 20:12
by ack
Is this a 2-level hierarchy or an arbitrarily-deep tree - Eric J. 2012-04-03 20:14
@Eric, just a simple 2-level hierarch - ack 2012-04-03 20:16


SELECT * FROM `data` ORDER BY COALESCE(`parent`, `id`), `parent`, `id`
2012-04-03 20:34
by Neil
This seems nice and clean... I don't suppose the sorting by title requirement can be worked into the ORDER BY clause can it - ack 2012-04-03 21:34
Got this to work, thanks for the lead! ORDER BY COALESCE (IF(parentID, parentTitle, NULL), IF(ID, title, NULL)), parentID, title -- Works in my actual query because I have a join that gives me the parentTitle which I can then swap into the COALESCE if needed - ack 2012-04-03 22:17
Sorry, I didn't notice the title requirement - Neil 2012-04-04 23:35


Here's a solution tested to work on SQL Server. Should be essentially the same on MySQL

select Id, Title, [Type], Id as OrderId from Hier h1 where [Type] = 1
select Id, Title, [Type], Parent as OrderId from Hier h2 where [Type] = 2
order by OrderId, [Type]
2012-04-03 20:31
by Eric J.


You said you wanted it to sort on the titles, correct?

SELECT id, title, parent
  ( SELECT id, title, parent,
    CASE WHEN parent is null THEN title ELSE CONCAT((SELECT title FROM `data` d2 WHERE = d.parent), '.', d.title) END AS sortkey
    FROM `data` d
   ) subtable
ORDER BY sortkey

edit: Edited to remove type from the query.

2012-04-03 20:39
by scwagner
Doing the string concatination and sorting on that result is fairly inefficient - Eric J. 2012-04-03 22:05