Help with query: Row number / Rank from a query... 2006-06-01 - By Michael Stassen
Back
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 @(protected) = O;
> SELECT @(protected):= @(protected) + 1 as row, SUM(IF(user = 'steve',@(protected),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 @(protected) = 0;
SELECT @(protected):= @(protected) + 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/mysql?unsub=mysql@(protected)
|
|
