CODEWITHSUNDEEP
mysqlsql
jason
jason

Reputation: 3962

get rank of specific users in mysql

I have a table of all users and I have another array which has subset of that table.
Ex:table contains 1,2,3,4,5,6,7,8,9,10

my users: 2,4,6,8,10:

Ranking of my users among themselves w.r.t points and has nothing to do with 1,3,5,7,9

I currently have this table:

create table uservotes(id int, name varchar(50), vote int);

INSERT INTO uservotes VALUES
(1, 'A', 34),
(2, 'B', 80),
(3, 'bA', 30),
(4, 'C', 8),
(5, 'D', 4),
(6, 'E', 14),
(7, 'F', 304),
(8, 'AA', 42),
(9, 'Ab', 6),
(10, 'Aa', 10);

How do I get ranking among 2,4,6,8,10

Answer I am looking for:

id    rank    votes   name
2       1       80     B
4       5        8     C
6       3       14     E
8       2       42     AA
10      4       10     Aa

I really appreciate any help.Thanks in Advance.

Upvotes: 0

Views: 241

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

I suspect that this is what you want:

select uv.*, (@rank := @rank + 1) as rank
from uservotes uv cross join
     (select @rank := 0) const
where uv.id in (2, 4, 6, 8, 10)
order by votes desc;

This is the standard way of calculating a rank efficiently in MySQL, along with a where clause to choose your ids.

EDIT:

Before starting, in most databases you would simply use row_number() or dense_rank() for this purpose. MySQL does not support this ANSI standard functionality.

The key is the variable @rank. The subquery const initializes this to 0. Then the query run. The where clause gets only the rows you are interested in. The order by then puts them in order by votes, with the biggest votes first. Finally, the @rank := @rank + 1 as rank both updates the @rank variable and assigns it to a column in the output.

Upvotes: 1

user3210897
user3210897

Reputation: 1

You need to loop a sql question with an dynamic variable something like 

select * from `uservotes` where id = $someones_id

$someones_id could be an array for an example

Upvotes: 0

Related Questions