How can I SELECT rows from a table when I MAX(ColA) and GROUP BY ColB

Question

I found this question which is very similar but I'm still having some troubles.

So I start with table named Scores

id | player | time | scoreA | scoreB |
~~~|~~~~~~~~|~~~~~~|~~~~~~~~|~~~~~~~~|
 1 | John   |  10  |   70   |   80   |
 2 | Bob    |  22  |   75   |   85   |
 3 | John   |  52  |   55   |   75   |
 4 | Ted    |  39  |   60   |   90   |
 5 | John   |  35  |   90   |   90   |
 6 | Bob    |  27  |   65   |   85   |
 7 | John   |  33  |   60   |   80   |

I would like to select the best average score for each player along with the information from that record. To clarify, best average score would be the highest value for (scoreA + scoreB)/2.

The results would look like this

id | player | time | scoreA | scoreB | avg_score |
~~~|~~~~~~~~|~~~~~~|~~~~~~~~|~~~~~~~~|~~~~~~~~~~~|
 5 | John   |  35  |   90   |   90   |    90     |
 2 | Bob    |  22  |   75   |   85   |    80     |
 4 | Ted    |  39  |   60   |   90   |    75     |

Based on the question I linked to above, I tried a query like this,

SELECT
   s.*,
   avg_score
FROM
   Scores AS s 
INNER JOIN (
   SELECT
      MAX((scoreA + scoreB)/2) AS avg_score,
      player,
      id
   FROM
      Scores
   GROUP BY
      player
) AS avg_s ON s.id = avg_s.id
ORDER BY
   avg_score DESC,
   s.time ASC

What this actually gives me is,

id | player | time | scoreA | scoreB | avg_score |
~~~|~~~~~~~~|~~~~~~|~~~~~~~~|~~~~~~~~|~~~~~~~~~~~|
 1 | John   |  10  |   70   |   80   |    90     |
 2 | Bob    |  22  |   75   |   85   |    80     |
 4 | Ted    |  39  |   60   |   90   |    75     |

As you can see, it has gotten the correct max avg_score, from record 5, but gets the rest of the information from another record, record 1. What am I missing? How do I ensure that the data all comes from the same record? I'm getting the correct avg_score but I want the rest of the data associated with that record, record 5 in this case.

Thanks in advance!

peterm · Answer 1 · 2013-07-10T22:53:12.797

3

Try

SELECT s.*,
       q.avg_score
  FROM scores s JOIN
(
  SELECT player, 
         MAX((scoreA + scoreB)/2) AS avg_score
    FROM scores
   GROUP BY player
) q ON s.player = q.player 
   AND (s.scoreA + s.scoreB)/2 = q.avg_score
 ORDER BY q.avg_score DESC, s.time ASC

Sample output:

| ID | PLAYER | TIME | SCOREA | SCOREB | AVG_SCORE |
----------------------------------------------------
|  5 |   John |   35 |     90 |     90 |        90 |
|  2 |    Bob |   22 |     75 |     85 |        80 |
|  4 |    Ted |   39 |     60 |     90 |        75 |

Here is SQLFiddle demo

edited Jul 10 '13 at 22:53

answered Jul 10 '13 at 22:41

peterm

91,357
15
148
157

@Kashi See updated answer (but Strawberry was first). You can't group by id and get the desired result (which is grouped by player). – peterm Jul 10 '13 at 22:55

score 3 · Accepted Answer · answered Jul 10 '13 at 22:49

3

 SELECT x.*
      , (scoreA+scoreB)/2 avg_score 
   FROM scores x 
   JOIN
      ( SELECT player, MAX((scoreA+scoreB)/2) max_avg_score FROM scores GROUP BY player) y 
     ON y.player = x.player 
    AND y.max_avg_score = (scoreA+x.scoreB)/2;

answered Jul 10 '13 at 22:49

Strawberry

33,750
13
40
57

This did give me the results I was wanting, so for that THANKS! But I was also wondering why joining ON y.id = x.id instead of ON y.max_avg_score = (x.scoreA+x.scoreB)/2 doesn't work? Perhaps I'm not familiar enough with how MAX() works. – Kashi Jul 10 '13 at 23:03
Luckily, volumes have been written on the topic, so I'll leave it at that. – Strawberry Jul 10 '13 at 23:21
Coming back to this nearly 7 years later and 7 years wiser, I appreciate the solution but the lack of explanation and the dismissive response to my comment is disappointing to see. – Kashi Jun 09 '20 at 03:59

How can I SELECT rows from a table when I MAX(ColA) and GROUP BY ColB

2 Answers2