Order by highest average position across multiple columns

Question

I'm in a tricky situation, I need to order the rows in one table by the highest position in three of the columns.

For example, here is a basic table;

ID  Stat1   Stat2   Stat3
--------------------------
1   400     100     200
2   200     200     100
3   100     400     400
4   300     300     300

Idealy, each row would be ordered by its average position in each column, sorted by the lowest ascending. This is what would ideally be returned:

ID  Average
------------
3   2
4   2
2   2.3333333333333
1   2.6666666666667

The first ID gets the lowest position, because for the first, second and third stats it ranks 1 4 and 3, which is an average of 2.6666666666667.

Answer 1

How about this?

SELECT id, (rank_by_stat1 + rank_by_stat2 + rank_by_stat3) / 3 AS avg
FROM (
    SELECT id, @rank_by_stat1 := @rank_by_stat1 + 1 AS rank_by_stat1
    FROM test, (SELECT @rank_by_stat1 := 0) init
    ORDER BY stat1 DESC
) stat1 INNER JOIN (
    SELECT id, @rank_by_stat2 := @rank_by_stat2 + 1 AS rank_by_stat2
    FROM test, (SELECT @rank_by_stat2 := 0) init
    ORDER BY stat2 DESC
) stat2 USING(id) INNER JOIN (
    SELECT id, @rank_by_stat3 := @rank_by_stat3 + 1 AS rank_by_stat3
    FROM test, (SELECT @rank_by_stat3 := 0) init
    ORDER BY stat3 DESC
) stat3 USING(id)
ORDER BY avg;
+------+--------------------+
| id   | avg                |
+------+--------------------+
|    3 |                  2 |
|    4 |                  2 |
|    1 | 2.6666666666666665 |
|    2 | 3.3333333333333335 |
+------+--------------------+
4 rows in set (0.00 sec)

How it works

It's very simple. I have modifed variable names to self-explained. It would be helpful see output of one of most inner query:

SELECT id, @rank_by_stat1 := @rank_by_stat1 + 1 AS rank_by_stat1, stat1
FROM test, (SELECT @rank_by_stat1 := 0) init
ORDER BY stat1 DESC
+------+---------------+-------+
| id   | rank_by_stat1 | stat1 |
+------+---------------+-------+
|    1 |             1 |   400 |
|    4 |             2 |   300 |
|    2 |             3 |   200 |
|    3 |             4 |   100 |
+------+---------------+-------+