tag:blogger.com,1999:blog-15319370.post8192519070945540628..comments2024-03-05T11:16:00.846+01:00Comments on Roland Bouman's blog: MySQL: Another Ranking trickrpboumanhttp://www.blogger.com/profile/13365137747952711328noreply@blogger.comBlogger43125tag:blogger.com,1999:blog-15319370.post-4109765647522454032014-11-13T16:15:59.805+01:002014-11-13T16:15:59.805+01:00This comment has been removed by the author.Anonymoushttps://www.blogger.com/profile/17963315605036508294noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-26989048206778939712013-01-11T01:25:54.889+01:002013-01-11T01:25:54.889+01:00I'm trying to wrap my head around this. I'...I'm trying to wrap my head around this. I'm not sure if I'm misunderstanding it, or a few different problems are getting mixed up. I think the problem that the MySQL docs refer to is not the same as the problem that you run into with ranking.<br /><br />@a:=@a+1 is a single statement and not vulnerable to execution order changes. However, if you have that, and then do an if() comparison, you might not get what you expect, and that is what the MySQL doc is referring to.<br /><br />The problem that you run into when ranking, is that the field selection (SELECT @a:=@a+1) does not need to happen row-by-row after it's been ordered. In one example I have, if I order a specific column it works correctly, but if I try to use a function it fails (probably doing the calculation before the ordering).<br /><br />As Roland points out, if you use a subquery for the order statement, and do the count in an outer query, I think you should be safe. So far I haven't been able to see a problem, but I would welcome further testing or info about what might mess it up.Joshnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-52990770127520668712012-12-11T13:25:44.220+01:002012-12-11T13:25:44.220+01:00Sylvain,
no. MySQL 5.6 does not have window funct...Sylvain,<br /><br />no. MySQL 5.6 does not have window functions or any other technique that I know of that would make this easier or faster.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-86580573072181301632012-12-11T11:43:37.066+01:002012-12-11T11:43:37.066+01:00Hi,
Since this article is from 2009, I wonder wet...Hi,<br /><br />Since this article is from 2009, I wonder wether mysql has now evolved on one or the other ranking technic?<br /><br />I mean, is there a scalable and reliable way to get rank number now?<br /><br />Best regards<br /><br />Anonymoushttps://www.blogger.com/profile/03189628772548932181noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-70114125256884595712012-02-29T19:17:27.626+01:002012-02-29T19:17:27.626+01:00I just learned a lot from your article. Thank you....I just learned a lot from your article. Thank you.<br /><br />AndreaAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-9210612567780237932011-09-19T22:01:35.199+02:002011-09-19T22:01:35.199+02:00No problem Keith. HtH :)No problem Keith. HtH :)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-13095892622428438822011-09-19T21:33:15.482+02:002011-09-19T21:33:15.482+02:00Thanks for a good discussion Roland :)Thanks for a good discussion Roland :)Keithnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-18395534061376940792011-09-19T21:15:12.875+02:002011-09-19T21:15:12.875+02:00Keith,
initializing the var before the actual qu...Keith, <br /><br />initializing the var before the actual query is fine too - it shouldn't matter for the result. the join is just a trick to make it self-contained.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-36211824098802859442011-09-19T18:48:54.875+02:002011-09-19T18:48:54.875+02:00I cannot get the INNER JOIN to work as it tells me...I cannot get the INNER JOIN to work as it tells me I have a mysterious syntax error at the INNER JOIN. I can, however, set the @rank variable before the ranking query. Any issues with doing it this way?<br /><br />SET @rank:=0;<br />SELECT @rank:=@rank+1, ...Keithnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-46358241646567962011-09-19T17:11:39.095+02:002011-09-19T17:11:39.095+02:00Keith,
maybe you can still use the rownum trick ...Keith, <br /><br />maybe you can still use the rownum trick if you force execution order. For instance you could try:<br /><br />SELECT @rank:=@rank+1, ..columns...<br />FROM (<br /> ... original query incl. ORDER BY clause...<br />)<br />INNER JOIN (SELECT @rank:=0)rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-89993239562858753712011-09-19T16:59:50.591+02:002011-09-19T16:59:50.591+02:00Right, which is the point of your post. I also rea...Right, which is the point of your post. I also realized this weekend that the rownum solution does not give the same ranks for the same scores :/Keithnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-52068596006165380742011-09-17T01:28:18.526+02:002011-09-17T01:28:18.526+02:00@Keith, yes, when you're in a situation where ...@Keith, yes, when you're in a situation where you can use this trick, then that is the fastest solution. But you always have to be aware of this documented limitation:<br /><br />"<br />As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change based on the elements contained within a given statement; in addition, this order is not guaranteed to be the same between releases of the MySQL Server. In SELECT @a, @a:=@a+1, ..., you might think that MySQL will evaluate @a first and then do an assignment second. However, changing the statement (for example, by adding a GROUP BY, HAVING, or ORDER BY clause) may cause MySQL to select an execution plan with a different order of evaluation.<br />"<br /><br />http://dev.mysql.com/doc/refman/5.5/en/user-variables.html<br /><br />In my experience, the ranking result can become unstable after adding an index, joining to other tables, adding an order by or adding a specific group by clause. Technically it's possible that a server upgrade could change the results as well.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-72109096944110421152011-09-17T01:10:49.665+02:002011-09-17T01:10:49.665+02:00I implemented this strategy for ranking today and ...I implemented this strategy for ranking today and it works great! Unfortunately, once I tried it with our Live dataset I noticed some warnings during execution. As mentioned by yourself and Shlomi there is a memory limit, and I seem to be hitting that limit even after setting group_concat_max_len to max_allowed_packets, which is 16M.<br /><br />Warning | 1260 | 9 line(s) were cut by GROUP_CONCAT()<br /><br />It makes sense this would happen because we are creating a giant string in memory that is very dependent on the size of the dataset. I'm afraid this solution may not be very scalable :/ As a gauge, our dataset was a little over 250,000.<br /><br />The solution we ended up with uses a rownum technique. The query takes 1.5-2 seconds on our dataset of 250,000.<br /><br />insert into player_stat_rankings SELECT t.player_id, t.score, @rownum:=@rownum+1 AS rank FROM (SELECT @rownum:=0) r, player_stat t order by score desc;Keithnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-26418637717792027252011-08-22T03:41:44.649+02:002011-08-22T03:41:44.649+02:00I really liked the article, and the very cool blog...I really liked the article, and the very cool blogAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-34966305286619220992011-05-14T02:20:26.096+02:002011-05-14T02:20:26.096+02:00Does anyone know how I'd make this an actual u...Does anyone know how I'd make this an actual update statement. I want to take the rand-id and insert that value to my eventrank filed. <br /><br />SELECT event, totallength<br />, FIND_IN_SET(<br /> totallength<br /> , (SELECT GROUP_CONCAT(<br /> DISTINCT totallength<br /> ORDER BY totallength DESC<br /> )<br /> FROM eresults)<br /> ) as rank<br />FROM eresults where event='Med Ball' and status !='DNC';<br /><br />The above gives me what I'd expect. I've tried the below with no luck.<br /><br />UPDATE eresults JOIN(<br />SELECT event, totallength<br />, FIND_IN_SET(<br /> totallength<br /> , (SELECT GROUP_CONCAT(<br /> DISTINCT totallength<br /> ORDER BY totallength DESC<br /> )<br /> FROM eresults)<br /> ) as rank<br />FROM eresults where event='Med Ball' and status !='DNC')<br />ranks ON (ranks.id = eresults.id)<br />SET eventrank = ranks.rank;Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-84338140360417608762011-05-05T02:39:01.489+02:002011-05-05T02:39:01.489+02:00Good day sir, I have a problem regarding my rankin...Good day sir, I have a problem regarding my ranking script, I already apply your tutorial but unfortunately if there is a tie with the average it will look to other columns to compare for tie breaker, Thank you very much in advance<br /><br /> Rank Name ave C1 C2<br /> 1 Mark Gil Guevarra 26.80 07.95 08.25 <-winner<br /> 2 Jess Menes 24.25 07.35 07.50 <-winner <br /> 3 Jef Llares 24.15 07.20 07.20 <-this will be change to rank 4<br /> 3 Noriel Arguelles 24.15 07.20 07.30 <-winner<br /> 4 James Bandiez 21.40 07.05 06.45 <-and this to rank 5<br /> 4 RObert Tribiana 21.40 07.05 05.50 <-and this to rank 5<br /> 5 Julius Kahugan 17.50 05.85 05.25 <-and this to rank 6Roberthttp://armaments.byethost6.comnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-338014611052166512011-01-28T01:05:00.952+01:002011-01-28T01:05:00.952+01:00Hi,
I'm quite new in MySQL but I have to crea...Hi,<br /> I'm quite new in MySQL but I have to create a ranking based on a specific SELECT using joined tables. My original select is this:<br /><br />SELECT<br /> l.cod_loja,<br /> l.nome_loj,<br /> SUM(c.vlrbase) AS soma<br />FROM<br /> tab_comissao c INNER JOIN tab_prot p ON (c.ade=p.ade)<br /> INNER JOIN tab_lojas l ON (l.cod_loja=p.cod_loja)<br />WHERE c.ade = p.ade AND p.cod_loja = l.cod_loja<br /> AND c.datalanc>='2011-01-04'<br /> AND c.datalanc<='2011-02-02'<br />GROUP BY l.cod_loja<br />ORDER BY soma DESC<br /><br />Where gives me this result:<br /><br />3 store-3 345874.54<br />7 store-7 213022.86<br />6 store-6 209934.94<br />...<br />...<br />...<br />57 store-57 2827.18<br /><br />So, how I could to this in order to show me the correct ranking?<br /><br />Thanks in advance,<br /><br />Kleyber DerickAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-30702869035588238442010-10-28T20:08:31.630+02:002010-10-28T20:08:31.630+02:00Along the same lines... Here's my problem you...Along the same lines... Here's my problem you may be able to help me with...<br /><br />The premise [I manage a web site http://SaveOregonWrestling.org, I'm adding a page (Fight Ladder) to list donors who specify their donation for a specific weight class... NCAA wrestling has 10 weight classes... - I want the top 3 donors listed in each weight class, ranked by their donation amount.] I am trying to implement a MySql query on a single table:<br /><br />table = FightLadder<br />columns = ID, PublishName, WeightClass, Total<br /><br />Below is the current PHP code I'm using which works as long as there's only 30 rows in the table. The moment I add row 31, the results are wack. e.g., a donor who donated in weight class 149 gets moved up to weight class 141 in the list!! : ( I also get a stream of "MySql WARNING error messages about moving to the next row", unless I add the @ in front of my var code, e.g. $champ1a = @mysql_result($result, 0,0);<br /><br />[code]<br /><br />$query="SELECT c.PublishName, c.State, c.Total, c.WeightClass, d.ranknum<br />FROM FightLadder AS c<br /> INNER JOIN (<br /> SELECT a.ID, COUNT(*) AS ranknum<br /> FROM FightLadder AS a<br /> INNER JOIN FightLadder AS b ON (a.WeightClass = b.WeightClass) AND (a.Total <= b.Total)<br /> GROUP BY a.ID<br /> HAVING COUNT(*) <= 3<br /> ) AS d ON (c.ID = d.ID)<br />ORDER BY c.WeightClass, c.Total DESC, d.ranknum";<br /><br />$result = mysql_query($query)<br /><br />[/code]<br /><br />Here's where I got the above code (for SQL Server - Mine's MySql)<br /><br />http://rickosborne.org/blog/index.php/2008/01/07/sql-getting-top-n-rows-for-a-grouped-query/<br /><br />Can you help me with the correct code to produce a [$result = mysql_query($query)] where only the top 3 donors in each weight class are listed?<br /><br />Thanks in advance for your valuable time.<br /><br />-Paul, IT Volunteer<br />Save Oregon Wrestling Foundation<br />800-553-0135 ext. 105Paul Fenleyhttp://www.saveoregonwrestling.orgnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-60972055601147757252010-10-10T10:15:30.346+02:002010-10-10T10:15:30.346+02:00wow great work... i just tried this and it's l...wow great work... i just tried this and it's like a magic... hehehe i'll be using it for my tabulation... thank you very much for this trick...Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-31420779410190385342010-09-22T16:09:35.823+02:002010-09-22T16:09:35.823+02:00Vince,
the problem with user-defined variables i...Vince, <br /><br />the problem with user-defined variables is that there is no way to explicitly control the order in which the expressions that change the values of the user-defined variables are executed. <br /><br />In your particular example, you define an explicit ORDER BY clause, and you may think this takes care of the problem. However, the ORDER BY clause only requires that the result is returned in order, not that the actual operations are executed in order too. If you add/drop indexes that change the access pattern, you may find unexpected results.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-78826393744198500782010-09-22T14:54:37.546+02:002010-09-22T14:54:37.546+02:00Thank you Roland for your answer.
My bad, I didn&#...Thank you Roland for your answer.<br />My bad, I didn't read the hole article...<br />I did some testing and the results I get are valid.<br />Do you think that this method isn't reliable because MySQL documentation state it ? Or do you have a proof that it is really not reliable?<br /><br />Cheers<br /><br />VinceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-50587676316303271692010-09-22T14:24:12.212+02:002010-09-22T14:24:12.212+02:00Hi Vince,
thanks for sharing this. If you read th...Hi Vince,<br /><br />thanks for sharing this. If you read the first part of the article, the whole point of this article was to develop a method that does not rely on user-defined variables.<br /><br />I am well aware of the performance gains that can be achieved if you do use user-defined variables, and I have written several articles on this blog that do use this device. However, as I outlined in this article, and as is also documented in the MySQL Reference Manual, user-defined variables are not reliable if you read and assign them within the same statement. <br /><br />I hope that clears it up.<br /><br />kind regards,<br /><br />Roland.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-62451687771782575992010-09-22T14:13:55.347+02:002010-09-22T14:13:55.347+02:00My solution outperfom all of yours : I'm using...My solution outperfom all of yours : I'm using a variables to compare with the previous score in the dataset and than increase the rank or not (in case of same score)<br /><br />SSET @rank=0;<br />SET @previous_score=0;<br />SELECT<br /> CASE <br /> WHEN @previous_score<>score THEN @rank:=@rank+1 END AS temp1,<br /> CASE <br /> WHEN @previous_score<>score THEN @previous_score:=score<br /> ELSE @previous_score:=score END AS temp2,<br /><br /> @rank as rank, id_user, score<br /> FROM users ORDER BY score DESC;<br /><br />I'm pleased to share this with you.<br /><br />VinceAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-15319370.post-72403088580719815882010-07-07T17:58:05.109+02:002010-07-07T17:58:05.109+02:00Anonymous,
if the formula does not rely on any a...Anonymous, <br /><br />if the formula does not rely on any aggregates, you can simply ORDER BY in the GROUP_CONCAT on that formula. <br /><br />Before I can answer the question with your particular formula, I would need to see your entire query as you would write it without any ranking.<br /><br />regards, Roland.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-15319370.post-32482736889510980722010-07-07T17:34:09.890+02:002010-07-07T17:34:09.890+02:00This is good but what if you are trying to rank fr...This is good but what if you are trying to rank from a formula!<br />e.g. (SUM(late) / SUM(deliveries)Anonymousnoreply@blogger.com