CODEWITHSUNDEEP
mysql
theStripes
theStripes

Reputation: 21

Mysql query to not show duplicate records

I have a leader board of high scores and need to not show the duplicate records for a more accurate leader board.

Table: highscores
+-------------------+----------------+-------------+-------+
| id |    name      |     time       |   moves     | score |
+-------------------+----------------+-------------+-------+
| 1  |  person1     |       33       |     22      |  245  |
+-------------------+----------------+-------------+-------+
| 2  |  person1     |       83       |     31      |  186  |
+-------------------+----------------+-------------+-------+

and my query is

SELECT * FROM highscores ORDER by Score DESC LIMIT 100

how can I change the query to only show the higherscore of duplicate records without messing up the descending part

this seems to be working

SELECT * FROM highscores GROUP BY name ORDER by Score DESC LIMIT 100

Upvotes: 0

Views: 56

Answers (1)

Bohemian
Bohemian

Reputation: 425348

Use mysql's custom group by:

SELECT * FROM (
   SELECT * FROM highscores
   ORDER by Score DESC) x
GROUP BY name
ORDER by Score DESC
LIMIT 100

This works because when not all non-aggregate columns are listed in the group by, mysql returns the first row encountered for each unique combination of the columns listed in the group by.

Upvotes: 1

Related Questions