0

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.

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Salil
  • 46,566
  • 21
  • 122
  • 156

1 Answers1

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • Thanks it works when i changed the above query with following one. Th eonly change i made is @rownum = @rownum + 1 to @rownum := @rownum + 1 SELECT t.*, @rownum := @rownum + 1 AS rank FROM tracks t, (SELECT @rownum := 0) r – Salil Dec 22 '09 at 07:24