CODEWITHSUNDEEP
mysqlsql
yahermann
yahermann

Reputation: 1569

Order by subgroup

I need help building a MySQL query that will output all rows in a certain order. The table definition contains id, sub_id, and name. The sub_id column is used to indicate that a given row should be grouped within id, and if sub_id is blank/0/null, then the row is a top-level row.

The desired sort order is by name, except I want all sub_id rows to be grouped within their corresponding id row. Within each subgroup, each row should be sorted by name.

For example, given this unordered table:

id   sub_id   name
=====================
1    0        bananas
2    0        apples
3    0        apricots
4    2        strawberries
5    2        cherries
6    1        oranges

The desired order is:

id   sub_id   name
=====================
2    0        apples
5    2        cherries
4    2        strawberries

3    0        apricots

1    0        bananas
6    1        oranges

(spacing between subgroups for illustrative purposes only)

Help? Thanks in advance!

Upvotes: 1

Views: 398

Answers (1)

Bulat
Bulat

Reputation: 6969

This should do it:

SELECT t.id, t.sub_id, t.name
FROM table t LEFT JOIN table p on t.sub_id = p.id
ORDER BY COALESCE(p.id, t.id), t.name

Upvotes: 1

Related Questions