Getting rank in mysql query

Question

I used this query to assign a rank to each name according to the votes they received, but it returns with an error:

1248 - Each view must have its own alias

Here is my code:

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

When changing the request to this: -

 SELECT @rownum: =@rownum +1 AS rank, name, vote FROM table ORDER BY vote DESC 

I get the expected query rank as NULL. Any help how to get a rank in the first place?

NOTE. I am not looking for an alternative solution. Just trying to do this in the request itself.

Answer 1

The error is pretty clear. Each view must have its own alias. You need to make an alias (SELECT @rownum := 0) as follows:

 SELECT @rownum := @rownum + 1 AS rank, name, vote FROM table, (SELECT @rownum := 0) t --This what you were missing an alias ORDER BY vote DESC 

SQL Fiddle Demo