开发者

to get top 5 rankings from database

In my table i have team and points column and I want to get top 5 teams .Teams with same points should be grouped and consider as one of the ranks so if 5 teams are having same points then all should come as one of the rank a开发者_运维百科nd next suceeding records according to team points


TRY

SELECT DISTINCT(point), team
FROM tableTeam
ORDER BY points DESC LIMIT 5


  SELECT team,
         points,
         (SELECT COUNT(*)
            FROM teams t2
           WHERE t2.points > t1.points) + 1 rank
    FROM teams t1
ORDER BY points DESC
   LIMIT 5


There's no window functions in MySQL, so you'll want to extract the rank in your scripts.

Also, if I'm making sense of your ranking criteria, you're actually interested in getting the top 5 teams plus any additional teams that might have the same number of points as that in the 5th rank.

If so, your limit should be applied to a subquery on the point criteria:

select name, points
from teams
where points >= (
      select points
      from teams
      order by points desc
      limit 1 offset 4
      )
order by points desc, name

If not, a simple order by/limit will do:

select name, points
from teams
order by points desc, name
limit 5
0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜