Current:
SELECT member_id
FROM
( SELECT m.member_id, m.group_id, m.screen_name,
a.mescount as mescount
FROM exp_members m,
( SELECT member_id, COUNT(*) as mescount
FROM editor_messages
WHERE editor_id = 27977
GROUP BY member_id) AS a
WHERE a.member_id = m.member_id
UNION
SELECT member_id, group_id, screen_name,
0
FROM exp_members
) as m
WHERE screen_name LIKE '%%'
AND group_id != 2
GROUP BY member_id
ORDER BY MAX(mescount) DESC,
CASE
WHEN group_id = 1 then 5
WHEN group_id = 5 then 4
WHEN group_id = 6 then 2
WHEN group_id = 9 then 3
WHEN group_id = 14 then 1
WHEN group_id = 15 then 6
ELSE 7 END ASC,
screen_name ASC
LIMIT 10
* Use tmp table (CREATE TEMPORARY TABLE ...) for the subqueries.
* Create an ordering table for the CASE. It would have pairs (1,5), (5,4), etc. Then JOIN to it to get the desired ordering.