Tutorial :MySQL Selecting wrong column value in Group By query



Question:

Here's a real noobish MySQL query problem I'm having.

I have a high score table in a game I'm writing. The high score DB records a name, level, and score achieved. There are many near duplicates in the db. For example:

Name | Level | Score | Timestamp (key)  Bob    2       41    | 1234567.890  Bob    3       15    | 1234568.890  Bob    3       20    | 1234569.890  Joe    2       40    | 1234561.890  Bob    3       21    | 1234562.890  Bob    3       21    | 1234563.890  

I want to return a "highest level achieved" high score list, with an output similar to:

Name | Level | Score  Bob    3       21  Joe    2       40  

The SQL Query I currently use is:

SELECT *, MAX(level) as level   FROM highscores   GROUP BY name  ORDER BY level DESC, score DESC  LIMIT 5  

However this doesn't quite work. The "Score" field output always seems to be randomly pulled from the group, instead of taking the corresponding score for the highest level achieved. Eg:

Name | Level | Score  Bob    3       41  Joe    2       40  

Bob never got 41 points on level 3! How can I fix this?


Solution:1

You'll need to use a subquery to pull the score out.

select distinct      name,       max(level) as level,      (select max(score) from highscores h2           where h2.name = h1.name and h2.level = h1.level) as score  from highscores h1   group by name   order by level desc, score desc  

Cheers,

Eric

It irks me that I didn't take the time to explain why this is the case when I posted the answer, so here goes:

When you pull back everything (*), and then the max level, what you'll get is each record sequentially, plus a column with the max level on it. Note that you're not grouping by score (which would have given you Bob 2 41, and Bob 3 21--two records for our friend Bob).

So, how the heck do we fix this? You need to do a subquery to additionally filter your results, which is what that (select max(score)...) is. Now, for each row that reads Bob, you will get his max level (3), and his max score at that level (21). But, this still gives us however many rows Bob has (e.g.-if he has 5 rows, you'll get 5 rows of Bob 3 21). To limit this to only the top score, we need to use a DISTINCT clause in the select statement to only return unique rows.

UPDATE: Correct SQL (can't comment on le dorfier's post):

SELECT h1.Name, h1.Level, MAX(h1.Score)      FROM highscores h1      LEFT OUTER JOIN highscores h2 ON h1.name = h2.name AND h1.level < h2.level      LEFT OUTER JOIN highscores h3 ON h1.name = h3.name AND h2.level = h3.level AND h1.score < h3.score      WHERE h2.Name IS NULL AND h3.Name IS NULL      GROUP BY h1.Name, h1.Level  


Solution:2

This is efficient.

SELECT h1.Name, h1.Level, h1.Score
FROM highscores h1
LEFT JOIN highscores h2 ON h1.name = h2.name AND h1.level < h2.level
LEFT JOIN highscores h3 ON h1.name = h3.name AND h1.level = h3.level AND h1.score < h3.score
WHERE h2.id IS NULL AND h3.id IS NULL

You're looking for the level/score for which there is no higher level for that user, and no higher score that that level.


Solution:3

Interesting problem. Here's another solution:

SELECT hs.name, hs.level, MAX(score) AS score  FROM highscores hs  INNER JOIN (    SELECT name, MAX(level) AS level FROM highscores GROUP BY name  ) hl ON hl.name = hs.name AND hl.level = hs.level  GROUP BY hs.name, hs.level;  

Personally, I find this the easiest to understand, and my hunch is that it will be relatively efficient for the database to execute.

I like the above query best, but just for kicks... I find the following one amusing in a kludgey sort of way. Assuming score can't exceed 99999...

SELECT name, level, score  FROM highscores hs  INNER JOIN (    SELECT name, MAX(level * 100000 + score) AS hfactor    FROM highscores GROUP BY name  ) hf ON hf.hfactor = hs.level * 100000 + hs.score AND hf.name = hs.name;  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »