> 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
> 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
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
ORDER BY points DESC;
It should give
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.
> 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.