SQL query not returning distinct values

PaPaB1nG0

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.

forpas

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-05-10

Comments

0 comments

SQL: SELECT DISTINCT not returning distinct values

SQL Query returning multiple values

Query not returning NULL values - SQL

SQL Query not returning Null values

SQL query not returning distinct values

SQL query not returning distinct values

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

How to import an asset in swift using Bundle.main.path() in a react-native native module

pump.io port in URL

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

How to remove the extra space from right in a webview?

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

flutter: dropdown item programmatically unselect problem

How to use merge windows unallocated space into Ubuntu using GParted?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Nuget add packages gives access denied errors

Svchost high CPU from Microsoft.BingWeather app errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

12.04.3--- Dconf Editor won't show com>canonical>unity option

Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

Any way to remove trailing whitespace FOR EDITED lines in Eclipse [for Java]?