Distinct entries from the set

user_3883930 0
Viewed 33

enter image description here

I am using the below query on the above database table but unable to get the distinct value. Help will be appreciated

SELECT DISTINCT doctor_user_id, doctor_name, score, time_in_seconds FROM basket_game_master WHERE game_id='$game_id' ORDER BY score DESC, time_in_seconds ASC LIMIT $limit

The goal is to retrieve the doctor name with unique doctor_user_id who have more score with less time_in_seconds

mysql sql database
1 Answers

You can use ROW_NUMBER():

SELECT doctor_user_id, doctor_name, score, time_in_seconds
FROM (SELECT bgm.*,
             ROW_NUMBER() OVER (PARTITION BY doctor_user_id ORDER BY score DESC, time_in_seconds ASC) as seqnum
      FROM basket_game_master bgm
      WHERE game_id = ?
     ) mgm
WHERE seqnum = 1
ORDER BY score DESC, time_in_seconds ASC
LIMIT ?;

Note the use of ? for the parameters. Do not munge the query string with literal values! That poses a risk for SQL injections and for hard-to-debug syntax errors.

user_11440350
Related