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

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

what's the error? also what is A in the where clause? – D. Rattansingh Jan 01 '14 at 21:44 — D. Rattansingh, Jan 01 '14 at 21:44

score 1 · Accepted Answer · edited Jan 02 '14 at 00:02

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'

edited Jan 02 '14 at 00:02

Ahmed Siouani

13,701
12
61
72

answered Jan 01 '14 at 22:00

peter.petrov

38,363
16
94
159

SELECT @i:=@i+1 AS rn, t.* FROM uservotes AS t, (SELECT @i:=0) AS tbl where name="A" ORDER BY vote desc ::::: **I tried the above code and this still gives me rank 1 not 4** – jason Jan 01 '14 at 22:03
@jason No, my code gives 4 for rn. Maybe you're looking wrongly at the ID not at the rn. See here. http://sqlfiddle.com/#!2/208045/5 The insert statements in your SQL Fiddle seem weird. Replay it all slowly :) – peter.petrov Jan 01 '14 at 22:03
Awesome it worked can you please explain it briefly what is t.* and x.name.I really appreciate it. – jason Jan 01 '14 at 22:07
1

@jason Sure. So x is the whole result set returned from my query 1). As you can see 1) is nested into 2). And t is just an alias in 1) for your main table - uservotes. And @i is doing the rownum simulation. – peter.petrov Jan 01 '14 at 22:09
@jason See Peter Johnson's answer (answered Aug 12 '10 at 17:26) here. http://stackoverflow.com/questions/1895110/row-number-in-mysql Basically I just adapted it for your case. – peter.petrov Jan 01 '14 at 22:12

D. Rattansingh · Answer 2 · 2014-01-01T22:07:59.913

0

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';

edited Jan 01 '14 at 22:07

answered Jan 01 '14 at 21:48

D. Rattansingh

1,569
3
19
30

no thats not right because the rank becomes 1 which is not true.That was the problem I am facing. – jason Jan 01 '14 at 21:51
So what are your expected results then, you didn't mention anything to that affect in your question. @jason The missing quotes around `A` would throw an error. – Funk Forty Niner Jan 01 '14 at 21:56
I'm pointing out that your where clause is in the wrong place. It should be after the subquery table t. I have no idea what you're trying to do – D. Rattansingh Jan 01 '14 at 21:59
Your SQLFiddle doesn't have a `rank 14` the only `14` in your fiddle is `(6, 'E', 14),` and it's the letter `E` not `A`. So like `D. Rattansingh` said, we have no idea what you're trying to do. @jason – Funk Forty Niner Jan 01 '14 at 22:01
I am trying to get the rank of name A which is 4 but I am unable to do it.I updated that in my question just now sorry. – jason Jan 01 '14 at 22:01
How is this rank being determined? – D. Rattansingh Jan 01 '14 at 22:06

score 0 · Answer 3 · answered Jan 01 '14 at 21:54

0

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

answered Jan 01 '14 at 21:54

Alex Monthy

1,827
1
14
24

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

3 Answers3