List:General Discussion« Previous MessageNext Message »
From:Dan Date:June 2 2006 7:28am
Subject:Re: Help with query: Row number / Rank from a query...
View as plain text  
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? 
>> unsub=peter.brawley@stripped
>>
>>
>>
>> --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
>

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