CODEWITHSUNDEEP
mysqlsqldistinct
Charlie Ansell
Charlie Ansell

Reputation: 461

SQL query not returning distinct values

I have a game leaderboard comprised of 500 rows of data and I wrote a script to return that data and have no duplicate scores. However, I am getting duplicate scores returned to me. Here is my script.

SELECT DISTINCT 
    username, score, 
    FIND_IN_SET(score, (SELECT DISTINCT GROUP_CONCAT(score ORDER BY score DESC) 
                        FROM TPS_STATS)) AS rank
FROM 
    TPS_STATS 
ORDER BY
    rank ASC
LIMIT 100;

An example of the duplicate results I am seeing is posted as an image.

duplicates

Upvotes: 0

Views: 365

Answers (1)

forpas
forpas

Reputation: 164224

If your version of MySql is 8.0 then you can use row_number():

SELECT 
  username, 
  score, 
  row_number() OVER (ORDER BY score desc, username) rn 
FROM TPS_STATS 
ORDER BY score desc, username 
LIMIT 100

See the demo.
If it is lower:

select 
  username,
  score,
  (select count(*) from TPS_STATS where score > t.score) +
  (select count(*) from TPS_STATS where score = t.score and username < t.username) + 1
  rank
from TPS_STATS t
order by rank, username
limit 100

See the demo

Upvotes: 1

Related Questions