>I want to get a particular users 'rank' or row number from the query.
SELECT 1+COUNT(*) AS Rank
FROM results r1
INNER JOIN results r2 ON r1.points<r2.points
> 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.
> Dan T
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.8.0/353 - Release Date: 5/31/2006