List:General Discussion« Previous MessageNext Message »
From:Michael Stassen Date:June 1 2006 6:51pm
Subject:Re: Help with query: Row number / Rank from a query...
View as plain text  
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

Thread
Help with query: Row number / Rank from a query...Dan1 Jun
  • Re: Help with query: Row number / Rank from a query...Michael Stassen1 Jun
  • Re: Help with query: Row number / Rank from a query...Peter Brawley2 Jun
    • Re: Help with query: Row number / Rank from a query...Dan2 Jun