Thanks Peter,
That looks pretty good to me. I never would have figured that out on
my own.
Dan T
On Jun 1, 2006, at 4:06 PM, Peter Brawley wrote:
Dan,
>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
WHERE r1.user='Steve';
PB
-----
Dan wrote:
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
--MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
