Dan wrote:
I have a regular query lets say:
Better to show the real query, rather than a simplified version. Simplified
requests get you simplified answers. What seems like a simplification to you,
may in fact hide a crucial piece of information, thus preventing anyone from
giving you a solution to your real problem. We need the real query to give a
real answer.
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;
That's not your real query, as the table name seems to have changed from
"results" to "points".
but I get:
rank user
---------------
0 john
2 steve
0 bill
I don't think so. Your select has a column named row, but it's missing in your
output.
From your description of the problem, I cannot tell why you made the second
query so complicated. What is wrong with the following, simpler query?
SET @i = 0;
SELECT @i:= @i + 1 AS rank, user
FROM results
ORDER BY points DESC;
It should give
rank user
---------------
1 john
2 steve
3 bill
as the results. Isn't that what you want?
which does not really help me
Any ideas? I tried the manual, Google and the list with no sucsess.
Thanks,
Dan T
If you show us what you want, instead of something that isn't what you want,
someone may be able to tell you how to get it.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
