Order by highest average position across multiple columns

mysql, sql

Solution

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 |
+------+---------------+-------+

Problem

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.`

Original source