Finding the rank or index of some name in an array returned by query

Question

I am firing a query which returns minimum 1000 rows containing name, amount in order amount desc. I want the index of some name from that array, I don't know how to do it?

As I am using Ruby, I use arr.index(name)+1 method which returns the index properly. But I want some MySQL query which gives me only numbers for that particular name.

Answer 1

There is no ranking functionality in MySQL. The closest you can get is to use a variable:

SELECT t.*, 
       @rownum := @rownum + 1 AS rank
  FROM TABLE t, (SELECT @rownum := 0) r

That will add a column called rank to your output, that will be unique, incrementing, and starts at 1.