My table consists of two columns:
id | my_group
-------------
1 | 3
2 | 2
3 | 1
4 | no_group
5 | 3
6 | 1
7 | 2
8 | 3
9 | no_group
10 | 1
Can you please help me to get the correct sorting order presented below, using using MySQL?
id | my_group
-------------
10 | 1
6 | 1
3 | 1
9 | no_group
8 | 3
5 | 3
1 | 3
7 | 2
2 | 2
4 | no_group
The my_group column is sorted descending but if my_group is not equal "no_group", my_group should group the results first.
You could get this complicated order with following statement:
SELECT
e.id,
e.my_group
FROM
example e
LEFT JOIN (
SELECT
MAX(e1.id) maxid,
e1.my_group
FROM
example e1
WHERE
e1.my_group <> 'no_group'
GROUP BY e1.my_group
) t
ON
e.my_group = t.my_group
ORDER BY COALESCE(t.maxid, id) DESC, id DESC;
Explanation:
We do a left join with the subselect that returns the maximum id per group, if there is a group.
The result of
SELECT
e.id,
e.my_group,
t.maxid,
COALESCE(t.maxid, e.id)
FROM
example e
LEFT JOIN (
SELECT
MAX(e1.id) maxid,
e1.my_group
FROM
example e1
WHERE
e1.my_group <> 'no_group'
GROUP BY e1.my_group
) t
ON
e.my_group = t.my_group
is
id my_group maxid COALESCE(t.maxid, e.id)
10 1 10 10
6 1 10 10
3 1 10 10
9 no_group NULL 9
5 3 8 8
1 3 8 8
8 3 8 8
2 2 7 7
7 2 7 7
4 no_group NULL 4
So for the rows with 'no_group' we get NULL in our maxid column. For these rows we've got to take the id. COALESCE returns the first non-null value of it's arguments, so it returns the maximum id per group and the id value for the rows with 'no_group'.
Note:
If there would be NULL values instead of the string 'no_group' then you could simply omit the WHERE clause of the subselect. I would prefer this.
, id DESC to ORDER BY clause.