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)
)
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.
SELECT * FROM `data` ORDER BY COALESCE(`parent`, `id`), `parent`, `id`
title requirement 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 parentTitle which I can then swap into the COALESCE if needed - ack 2012-04-03 22:17
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.