mysql sort before group by

Question

I think this is the best solution. However, this query is not solve my issue - I have like this table:

+--+-------+-----+
|id|user_id|score|
+--+-------+-----+
|1 |1      |5    |
+--+-------+-----+
|2 |1      |16   |
+--+-------+-----+
|3 |1      |15   |
+--+-------+-----+

Query:

  SELECT *
    FROM (`_scorboard`)
GROUP BY `user_id`
  HAVING `score` = MAX(score)
ORDER BY `score` desc 

result 0 rows

Why is it returns 0 records ?

Is this even executing? `SELECT * ... GROUP BY ...` is an odd thing to be doing! — Will A, May 08 '11 at 23:04
[here](http://stackoverflow.com/questions/5140785/mysql-order-before-group-by/5140943#5140943), such a solution are given by — alioygur, May 08 '11 at 23:10
I solved it like this, but I do not think it is a correct solution SELECT t1.*, CONCAT(t2.firstname, ' ', t2.lastname) AS fullname FROM (SELECT * FROM _scorboard ORDER BY score DESC) AS t1 LEFT JOIN _user_profile AS t2 ON(t1.user_id = t2.user_id) GROUP BY t1.user_id LIMIT 0, 20 — alioygur, May 08 '11 at 23:12

OMG Ponies · Accepted Answer · 2011-05-08T23:22:14.037

Use:

SELECT a.*
  FROM SCOREBOARD a
  JOIN (SELECT t.user_id,
               MAX(t.score) AS max_score
          FROM SCOREBOARD t
      GROUP BY t.user_id) b ON b.max_score = a.score
                           AND b.user_id = a.user_id

If you want those who have the highest score in the table:

SELECT a.*
  FROM SCOREBOARD a
  JOIN (SELECT MAX(t.score) AS max_score
          FROM SCOREBOARD t) b ON b.max_score = a.score

score 3 · Answer 2 · answered May 08 '11 at 23:17

Since you have a GROUP BY clause in your query, MySQL groups by the user_id of 1 first, choosing any of the rows that it pleases. The HAVING clause then applies to these selected rows. Since the selected row may or may not be the one with the MAX value of score, the query is returning 0 results.

The correct way to do is:

  SELECT _scoreboard.*
    FROM _scoreboard JOIN (SELECT user_id, MAX(score)
                           FROM _scorboard
                           GROUP BY user_id)
      AS t ON _scoreboard.user_id = t.user_id
          AND _scoreboard.score = t.score
ORDER BY _scoreboard.score DESC

Thanks for actually answering the question! This was the solution I learnt something from! :) — Chris Kempen, Mar 06 '12 at 09:23

mysql sort before group by

2 Answers2