mysql sort before group by

I think t开发者_StackOverflowhis 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 ?

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

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