Re: Help with query: Row number / Rank from a query...
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 I have a regular query lets say: SELECT user, points FROM results ORDER BY points DESC so I get: userpoints --- john23 steve17 bill14 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: rankuser --- 0john 2steve 0bill 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? [EMAIL PROTECTED] --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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query: Row number / Rank from a query...
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 I have a regular query lets say: SELECT user, points FROM results ORDER BY points DESC so I get: userpoints --- john23 steve17 bill14 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: rankuser --- 0john 2steve 0bill 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/[EMAIL PROTECTED] --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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with query: Row number / Rank from a query...
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: userpoints --- john23 steve 17 bill14 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: rankuser --- 0john 2steve 0bill 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 rankuser --- 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/[EMAIL PROTECTED]
Help with query: Row number / Rank from a query...
I have a regular query lets say: SELECT user, points FROM results ORDER BY points DESC so I get: userpoints --- john23 steve 17 bill14 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: rankuser --- 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/[EMAIL PROTECTED]
