I have a schema that essentially looks like this:
CREATE TABLE `data` ( `id` int(10) unsigned NOT NULL, `title` text, `type` tinyint(4), `parent` int(10) )
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.
SELECT * FROM `data` ORDER BY COALESCE(`parent`, `id`), `parent`, `id`
titlerequirement can be worked into the ORDER BY clause can it - ack 2012-04-03 21:34
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
parentTitlewhich I can then swap into the COALESCE if needed - ack 2012-04-03 22:17
titlerequirement - 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 union select Id, Title, [Type], Parent as OrderId from Hier h2 where [Type] = 2 order by OrderId, [Type]
You said you wanted it to sort on the titles, correct?
SELECT id, title, parent FROM ( SELECT id, title, parent, CASE WHEN parent is null THEN title ELSE CONCAT((SELECT title FROM `data` d2 WHERE d2.id = d.parent), '.', d.title) END AS sortkey FROM `data` d ) subtable ORDER BY sortkey
edit: Edited to remove
type from the query.