MySQL Sort Groups of rows

Question

I have a table menu which contains rows which refers to others rows in the same table via foreign keys.
Here the table:

+----+---------------+--------+
| id | title         | parent |
+----+---------------+--------+
| 1  | Enseignements | NULL   |
| 2  | Juments       | 4      |
| 3  | Étalons       | 4      |
| 4  | Animaux       | NULL   |
| 5  | Tarifs        | 1      |
+----+---------------+--------+

I would like to group rows according to hierarchical and alphabetical order, like this:

+----+---------------+--------+
| id | title         | parent |
+----+---------------+--------+
| 4  | Animaux       | NULL   |
| 3  | Étalons       | 4      |
| 2  | Juments       | 4      |
| 1  | Enseignements | NULL   |
| 5  | Tarifs        | 1      |
+----+---------------+--------+

I just managed to group items from the same branch. The sub-level items are sorted by title. In fact, I wish that all the first-level items are also sorted by title, like this:

+----+---------------+--------+
| id | title         | parent |
+----+---------------+--------+
| 1  | Enseignements | NULL   |
| 5  | Tarifs        | 1      |
| 4  | Animaux       | NULL   |
| 3  | Étalons       | 4      |
| 2  | Juments       | 4      |
+----+---------------+--------+

With the code :

SELECT title, COALESCE(parent, id), parent
FROM menu
GROUP BY COALESCE(parent, id), title

How can I do this ?

May be this:: http://stackoverflow.com/questions/11064913/achieve-hierarchy-parent-child-relationship-in-an-effective-and-easy-way help — Sashi Kant, Feb 05 '13 at 18:02

score 0 · Answer 1 · answered May 30 '13 at 06:06

SQL Fiddle

MySQL 5.5.30 Schema Setup:

CREATE TABLE Table1
    (`id` int, `title` varchar(13), `parent` varchar(4))
;

INSERT INTO Table1
    (`id`, `title`, `parent`)
VALUES
    (1, 'Enseignements', NULL),
    (2, 'Juments', '4'),
    (3, 'Étalons', '4'),
    (4, 'Animaux', NULL),
    (5, 'Tarifs', '1')
;

Query 1:

SELECT title, COALESCE(parent, id), parent
FROM Table1
GROUP BY 2,1
order by COALESCE(parent, id) desc,title asc

Results:

|         TITLE | COALESCE(PARENT, ID) | PARENT |
-------------------------------------------------
|       Animaux |                    4 | (null) |
|       Étalons |                    4 |      4 |
|       Juments |                    4 |      4 |
| Enseignements |                    1 | (null) |
|        Tarifs |                    1 |      1 |

MySQL Sort Groups of rows

1 Answers1