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