使用where子句在mysql中按降序获取人员的等级

Question

I have tried the http://sqlfiddle.com/#!2/df2ec/1 to get the data:I am trying to get the rank of name A which is 4 but I am unable to do it.

SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes, (SELECT @rownum := 0) t ORDER BY vote DESC

in data 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);

I am unable to get the where clause .Basically I want to know which rank is the name based on the votes .I tried :

SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes WHERE name= A , (SELECT @rownum := 0) t ORDER BY vote DESC

But I am getting an error.I really appreciate any help.Thanks in Advance.

Error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' (SELECT @rownum := 0) t ORDER BY vote DESC' at line 2: SELECT @rownum := @rownum + 1 AS rank, name, vote FROM uservotes WHERE name= A , (SELECT @rownum := 0) t ORDER BY vote DESC

Answer 1

1) Try this one:

SELECT 
    @i:=@i+1 AS rn, 
    t.*
FROM 
    uservotes AS t,
    (SELECT @i:=0) AS tbl
    ORDER BY vote desc

2) Then getting the particular row for A is easy:

select * From
(
SELECT 
    @i:=@i+1 AS rn, 
    t.*
FROM 
    uservotes AS t,
    (SELECT @i:=0) AS tbl
ORDER BY vote desc

) x where x.name = 'A'