SQL query not returning distinct values

Question

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.

There are no duplicates. Each row is different than the other. DISTINCT username, score, ... returns distinct rows of these columns and not distinct columns. Can you post what is your expected output? — forpas
– forpas, Commented Mar 9, 2019 at 21:49
I'm expecting it to not pick rows that have the same score because if it does as it has here then the rank will not be able to distinguish between two users with the same results — Charlie Ansell
– Charlie Ansell, Commented Mar 9, 2019 at 21:54
Then say for rank=1 which username would be selected? You do want usernames, don't you? — forpas
– forpas, Commented Mar 9, 2019 at 21:56
Then this is the result you need. All users ranked by their score with ties. — forpas
– forpas, Commented Mar 9, 2019 at 22:02

forpas · Accepted Answer · 2019-03-09 23:10:55Z

1

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

answered Mar 9, 2019 at 23:10

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query not returning distinct values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related