List:General Discussion« Previous MessageNext Message »
From:Dan Date:June 1 2006 3:25pm
Subject:Help with query: Row number / Rank from a query...
View as plain text  
I have a regular query lets say:

SELECT user, points FROM results ORDER BY points DESC

so I get:

user	points
---------------
john	23
steve	17
bill	14

 From this I want to get a particular users 'rank' or row number from  
the query.  I was hoping for a single line elegant query for this.

I thought of this:

SET @i = O;
SELECT @i:= @i + 1 as row, SUM(IF(user = 'steve',@i,0)) as rank, user  
FROM points ORDER BY points DESC GROUP BY id;

but I get:

rank	user
---------------
0	john
2	steve
0	bill

which does not really help me

Any ideas?  I tried the manual, Google and the list with no sucsess.

Thanks,

Dan T
Thread
Help with query: Row number / Rank from a query...Dan1 Jun
  • Re: Help with query: Row number / Rank from a query...Michael Stassen1 Jun
  • Re: Help with query: Row number / Rank from a query...Peter Brawley2 Jun
    • Re: Help with query: Row number / Rank from a query...Dan2 Jun