CODEWITHSUNDEEP
mysql
ack
ack

Reputation: 14935

mySQL hierarchical grouping sort

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.

Upvotes: 2

Views: 1096

Answers (3)

scwagner
scwagner

Reputation: 4005

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.

Upvotes: 0

Neil
Neil

Reputation: 55392

SELECT * FROM `data` ORDER BY COALESCE(`parent`, `id`), `parent`, `id`

Upvotes: 2

Eric J.
Eric J.

Reputation: 150108

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]

Upvotes: 0

Related Questions