Order by max value on distinct records, without using a subquery (mysql)

Question

I have the following table:

+-----------+--------+------+--------+----------+----------+---------+-------+
| player_id | europe | asia | africa | namerica | lamerica | oceania | total |
+-----------+--------+------+--------+----------+----------+---------+-------+
|    121693 |   1076 | 70   | NULL   | NULL     | NULL     | NULL    |  1146 |
|    121693 |    416 | 70   | NULL   | NULL     | NULL     | NULL    |   486 |
|    121693 |     40 | NULL | NULL   | NULL     | NULL     | NULL    |    40 |
|    118840 |     40 | NULL | NULL   | NULL     | NULL     | NULL    |    40 |
|    121693 |     40 | 70   | NULL   | NULL     | NULL     | NULL    |   110 |
|    118840 |     40 | 490  | NULL   | NULL     | NULL     | NULL    |   530 |
|    121691 |     96 | 0    | NULL   | NULL     | NULL     | NULL    |    96 |
|    121693 |    136 | 70   | NULL   | NULL     | NULL     | NULL    |   206 |
|    118840 |     40 | NULL | NULL   | NULL     | NULL     | NULL    |    40 |
|    121691 |     96 | NULL | NULL   | NULL     | NULL     | NULL    |    96 |
+-----------+--------+------+--------+----------+----------+---------+-------+

What I would like to get is something like:

+-----------+--------+------+--------+----------+----------+---------+-------+
| player_id | europe | asia | africa | namerica | lamerica | oceania | total |
+-----------+--------+------+--------+----------+----------+---------+-------+
|    121693 |   1076 |   70 | NULL   | NULL     | NULL     | NULL    |  1146 |
|    118840 |     40 |  490 | NULL   | NULL     | NULL     | NULL    |   530 |
|    121691 |     96 |    0 | NULL   | NULL     | NULL     | NULL    |    96 |
+-----------+--------+------+--------+----------+----------+---------+-------+

I have tried the following, but the results are not quite what I need.

SELECT player_id, europe, asia, africa, namerica, 
                lamerica, oceania, MAX(total) AS total 
FROM tbl 
WHERE total IS NOT NULL 
GROUP BY player_id 
ORDER BY total DESC

this is the result

+-----------+--------+------+--------+----------+----------+---------+-------+
| player_id | europe | asia | africa | namerica | lamerica | oceania | total |
+-----------+--------+------+--------+----------+----------+---------+-------+
|    121693 |   1076 |   70 | NULL   | NULL     | NULL     | NULL    |  1146 |
|    118840 |     40 |      | NULL   | NULL     | NULL     | NULL    |   530 |
|    121691 |     96 |    0 | NULL   | NULL     | NULL     | NULL    |     96 |
+-----------+--------+------+--------+----------+----------+---------+-------+

Please note that I am using a MySQL view. Views in MySQL do not allow subqueries.

Edit:

Based on prior suggestions, I have tried this:

SELECT a.player_id, a.europe, a.asia, a.africa, a.lamerica, a.namerica,    a.oceania, a.total 
FROM tbl AS a 
INNER JOIN (
    SELECT player_id, MAX(total) AS total 
    FROM tbl 
    GROUP BY player_id) b
ON a.player_id = b.player_id AND a.total = b.total

But I get these results:

+-----------+--------+------+--------+----------+----------+---------+-------+
| player_id | europe | asia | africa | lamerica | namerica | oceania | total |
+-----------+--------+------+--------+----------+----------+---------+-------+
|    121693 |   1076 | 70   | NULL   | NULL     | NULL     | NULL    |  1146 |
|    118840 |     40 | 490  | NULL   | NULL     | NULL     | NULL    |   530 |
|    121691 |     96 | NULL | NULL   | NULL     | NULL     | NULL    |   96  |
|    121691 |     96 | 0    | NULL   | NULL     | NULL     | NULL    |    96 |
+-----------+--------+------+--------+----------+----------+---------+-------+

It also returns a tie. How is it possible to avoid this?

I am interested to know why you thought `total AS total` was necessary — RiggsFolly, Feb 20 '17 at 15:59
@Barmar: I edited the question. I tried to solutions posted in other similar questions but I couldn't get it work. Any help? Thanks! — dimoss, Feb 20 '17 at 16:33
Add your attempt to use one of the solutions in the duplicate question. Then I can reopen and show how to correct it. — Barmar, Feb 20 '17 at 16:43
@Barmar: I updated with my attempt. Can you help me pls? Thanks. — dimoss, Feb 20 '17 at 16:58
As you can see I get player_id: 121691 two times because the total is the same. It should be only one. — dimoss, Feb 20 '17 at 17:15
@Barmar: Please reopen my question as it's different than the suggested solutions because in my case there is a tie in the max values. How could I eliminate the ties? — dimoss, Feb 21 '17 at 08:52
@dimoss What criteria should be used to break the ties? Add it to the subquery and the join. — Barmar, Feb 21 '17 at 21:33
@Barmar: There are not specific criteria. I just want to have only one and not all the ties. The data I gave here is a small piece. In the actual table there are a lot of ties. I want only one per 'player_id'. Thanks! — dimoss, Feb 22 '17 at 22:09
Add `group by a.player_id` at the very end. The contents of the other columns will be chosen arbitrarily from all the rows with ties. They might not come from the same rows as each other. — Barmar, Feb 22 '17 at 22:12

score 0 · Answer 1 · answered Feb 22 '17 at 22:16

Add another GROUP BY a.player_id to the query:

SELECT a.player_id, a.europe, a.asia, a.africa, a.lamerica, a.namerica,    a.oceania, a.total 
FROM tbl AS a 
INNER JOIN (
    SELECT player_id, MAX(total) AS total 
    FROM tbl 
    GROUP BY player_id) b
ON a.player_id = b.player_id AND a.total = b.total
GROUP BY a.player_id

This will select the contents of the other columns arbitrarily from the rows with ties.

If you have the ONLY_FULL_GROUP_BY SQL mode enabled (it's on by default starting with MySQL 5.7) you'll need to use the ANY_VALUE to indicate that this is OK.

SELECT a.player_id, ANY_VALUE(a.europe), ANY_VALUE(a.asia), ANY_VALUE(a.africa), ANY_VALUE(a.lamerica), ANY_VALUE(a.namerica), ANY_VALUE(a.oceania), ANY_VALUE(a.total)

Order by max value on distinct records, without using a subquery (mysql)

1 Answers1