0

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...

d-_-b
  • 21,536
  • 40
  • 150
  • 256
  • 1
    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
  • 1
    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
  • 2
    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

1 Answers1

0

This would be ok:

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