Can I order by multiple columns and somehow keep the ordering related between columns in MySQL?

Question

I know the title doesn't explain my question very well (if someone can come up with a better title then please edit it). Here's what I want to do, say I have the following table:

id | a  | b  | c 
------------------
1  | 3  | 3  | 3
2  | 20 | 40 | 30
3  | 40 | 30 | 10
4  | 30 | 10 | 15
5  | 10 | 15 | 6
6  | 15 | 6  | 20

This is slightly truncated version, I have a few more columns to sort by, but the principle behind the data & my question is the same.

What I would like is to get the data ordered in the following way:

1. The row with the highest value in col a
2. The row with the highest value in col b
3. The row with the highest value in col c
4. Followed by all remaining rows ordered by their value in col c

So, the result set would look like:

id | a  | b  | c 
------------------
3  | 40 | 30 | 10
2  | 20 | 40 | 30
6  | 15 | 6  | 20
4  | 30 | 10 | 15
5  | 10 | 15 | 6
1  | 3  | 3  | 3

Doing a

SELECT id, a, b, c 
FROM table
ORDER BY a DESC, b DESC, c DESC

Obviously gives me a ordered first, then b and finally c, so the following (which is not what I need):

id | a  | b  | c 
------------------
3  | 40 | 30 | 10
4  | 30 | 10 | 15
2  | 20 | 40 | 30
6  | 15 | 6  | 20
5  | 10 | 15 | 6
1  | 3  | 3  | 3

Answer 1

I'm not familiar with the MySQL TSQL dialect but you would have to first SELECT the row with the highest 'A' value, perform a UNION ALL (i.e. no distinct via sorting) with the row with the highest 'B' value, perform a UNION ALL with the row with the highest 'C' value and then a UNION ALL with the remaining rows ordered by 'C' and excluding the 3 rows (by id) already selected.

Answer 2

I've just tested the following which appears to work (does involve 3 subqueries however):

SELECT id, a, b, c
FROM test 
ORDER BY FIELD(a,(SELECT MAX(a) FROM test)) DESC, 
FIELD(b,(SELECT MAX(b) FROM test)) DESC, 
FIELD(c,(SELECT MAX(c) FROM test)) DESC, 
c DESC