CODEWITHSUNDEEP
phpmysqlsql
jason
jason

Reputation: 3962

get the rank of the person in descending order in mysql with where clause

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

Upvotes: 0

Views: 846

Answers (3)

peter.petrov
peter.petrov

Reputation: 39477

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'

Upvotes: 1

D. Rattansingh
D. Rattansingh

Reputation: 1669

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

To get the rank:

select rank, name, vote
from (SELECT @rownum := @rownum + 1 AS rank, name, vote 
FROM uservotes, (SELECT @rownum := 0) t ORDER BY vote DESC) z
where name='A';

Upvotes: 0

Alex Monthy
Alex Monthy

Reputation: 1877

select a.name, (select count(*) + 1 from uservotes b where (a.vote > b.vote)) as rank 
from uservotes a having name="A" order by rank

Upvotes: 0

Related Questions