determine which row number a result is - mysql

Question

How could I find which number result a specific row is in a table?

For example, a table with names and ratings (ratings do not stay constant),

| name  |  rating  |
+-------+----------|
|  me   +     14   |
+-------+----------|
|  you  +    15    |
+-------+----------|

The query can only select one row at a time, so I'd like to incorporate this into it.

select name,rating,?????? from table where name = 'you' ORDER BY rating

how can i return 'you','15','2'?

i get the feeling i'm missing something very simple here...

Why not include an AUTO_INCREMENT column in the table, or otherwise include an incrementing integer you can select? — Martin Atkins, Mar 18 '13 at 00:02
ultimately the order will change, so it won't be based on any static column value... ratings will fluctuate for example — d-_-b, Mar 18 '13 at 00:03
Ahh, so you want the row's index in the resultset *after ordering by rating*. You didn't mention the ordering in your question. — Martin Atkins, Mar 18 '13 at 00:04
Well, there's this: http://stackoverflow.com/questions/3126972/mysql-row-number — minopret, Mar 18 '13 at 00:06
It seems like you're trying to do a "ranking" query, as described in this article? http://thinkdiff.net/mysql/how-to-get-rank-using-mysql-query/ — Martin Atkins, Mar 18 '13 at 00:06

score 0 · Answer 1 · answered Mar 18 '13 at 00:05

0

This would be ok:

SELECT @i:=@i+1 AS iterator, t.name, t.rating
FROM table t,(SELECT @i:=0) foo

answered Mar 18 '13 at 00:05

johnnaras

1 Answers1