开发者

MySQL Select and Order by with a Limit that can change

开发者 https://www.devze.com 2023-02-09 01:22 出处:网络
So I have the table: iduidpointsgameid -------------------------------------- 134502 212开发者_运维问答 402

So I have the table:

id       uid       points       gameid
--------------------------------------
1        34        50           2
2        12       开发者_运维问答 40           2
3        45        30           2
4        40        30           2
5        23        20           2

So I my goal is to get the top three players based on points. So in the event uid 40 above had 20 pts the following query would work for me:

SELECT * FROM table WHERE gameid = 2 ORDER BY points DESC LIMIT 3

But this only works when the top three people have different points. I need a way to get the top three players but select more if points are the same.


SELECT t.*
FROM table t JOIN (
    SELECT points
    FROM table r
    WHERE r.gameid = 2
    ORDER BY r.points DESC
    LIMIT 3
) tr ON t.points = tr.points
WHERE t.gameid = 2
0

精彩评论

暂无评论...
验证码 换一张
取 消