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.).
精彩评论