CODEWITHSUNDEEP
mysqlsqlsql-order-by
user1561346
user1561346

Reputation: 502

ORDER BY before GROUP BY for leaderboard

I have a table with all players results:

  id  |result| user_id
------------------------
  1   | 130  | 5C382072
  2   | 145  | 5C382072
  3   | 130  | 8QHDTz7w
  4   | 166  | 6155B6D0
  5   | 100  | DFSA3444

Smaller result is better. I need to make query for leaderboard. Each player must appear once in leaderboard with his best result. If 2 players have equal results, the one with smaller id should appear first. So I'm expecting this output:

  id  |result| user_id
------------------------
  5   | 100  | DFSA3444
  1   | 130  | 5C382072
  3   | 130  | 8QHDTz7w
  4   | 166  | 6155B6D0

I can't get desired result, cause grouping by user_id goes before ordering it by result, id.

My code: 

SELECT id, MIN(result), user_id
FROM results
GROUP BY user_id
ORDER BY result, id

It output something close to desired result, but id field is not connected to row with smallest user result, it can be any id from group with the same user_id. Because of that ordering by id not work at all.

EDIT:

What I didn't mention before is that I need to handle situations when user have identical results.

I came up with two solutions that I don't like. :)

1) A bit slow and ugly:

SELECT t1.*
FROM (SELECT * FROM results WHERE results_status=1) t1 
LEFT JOIN  (SELECT * FROM results WHERE results_status=1) t2 
  ON (t1.user_id = t2.user_id AND (t1.result > t2.result OR (t1.result = t2.result AND t1.id > t2.id)))
WHERE t2.id IS NULL
ORDER BY result, id

2) Ten times slower but more clear:

SELECT *
FROM results t1
WHERE id = (
  SELECT id 
  FROM results 
  WHERE user_id = t1.user_id AND results_status=1 
  ORDER BY result, id 
  LIMIT 1
)
ORDER BY result, id

I'm stuck. :(

Upvotes: 1

Views: 79

Answers (2)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95572

This should get you close. Avoid MySQL's lenient (errant) GROUP BY syntax, which lets you form a GROUP BY clause without naming unaggregated columns from the SELECT list. Use standard SQL's GROUP BY syntax instead.

select t.user_id, m.min_result, min(t.id) id
from results t
inner join (select user_id, min(result) min_result
            from results
            group by user_id) m
        on t.user_id = m.user_id
       and t.result = m.min_result
group by t.user_id, m.min_result

Upvotes: 1

wils484
wils484

Reputation: 275

Edit: I think you need a subquery:

SELECT a.id, a.result, a.user_id FROM results a 
WHERE a.user_id, a.result IN (SELECT b.user_id, MIN(b.result) FROM results b 
    GROUP BY b.user_id)
ORDER BY a.user_id

This will return an undefined id if the same user had the same score more than once, but will order the users correctly and will match id to the correct user_id.

Upvotes: 1

Related Questions