开发者

How to select filed position by votes MySQL?

I've got a database table called servers with three columns 'id', 'name', and 'votes'.

How can I select the position of column id 5 by 开发者_如何转开发votes?

Example, I want to check which position server 3 is in by votes in my competition?


If I've interpreted your question correctly, you are asking how to find the rank of the row with id 5 in a list of servers sorted by votes. There is a complex solution, which requires sorting, but the easier solution which can be done in O(log(n)) space and O(n) time is to simply measure the number of votes for id = 5

select votes from servers where id = 5;

and then walk through the database and add one for every server encountered that has smaller number of votes. Alternatively, you can do something like:

select count(*) from servers where votes <= %votes

It is excessive to sort this (O(nlog(n) time) when you can simple iterate through the entire list once and gather all the information you need.


Use LIMIT:

SELECT id, name, votes FROM servers ORDER BY votes DESC LIMIT 2,1;

LIMIT a, b means "give me b rows, starting at row a", and a is zero-based.


OK, I misunderstood. Now. Suppose your server has 27 votes.

SELECT COUNT(*) FROM servers WHERE votes < 27;

Your server's rank will be 1 plus the result; ties are possible (i.e. ranks will be like 1, 2, 3, 3, 3, 6, 7, 7, 9 etc.).

0

上一篇:

下一篇:

精彩评论

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

最新问答

问答排行榜