tag:blogger.com,1999:blog-17155656.post114488102840870698..comments2014-03-08T05:19:51.791+01:00Comments on db4free.net blog: A little (?) brain exerciseMarkus Popphttp://www.blogger.com/profile/15355530354397508921noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-17155656.post-16171929113437623542008-03-09T17:01:00.000+01:002008-03-09T17:01:00.000+01:00Probably not for this problem but a logical soluti...Probably not for this problem but a logical solution for a mystical problem in mysql older versions..<BR/><BR/><BR/>http://formyhelp.blogspot.com/2008/03/error1235-this-version-of-mysql-doesnt.htmlacpmasqueradehttps://www.blogger.com/profile/09982379608697036427noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144967006943905562006-04-14T00:23:00.000+02:002006-04-14T00:23:00.000+02:00Hi Roland,it seems, you got it right - the three h...Hi Roland,<BR/><BR/>it seems, you got it right - the three highest (not only distinct) values from d2 for each of the 20 d1 values (if there are e.g. two values with 999,999, they should show up twice).<BR/><BR/>If there are two equal values on 3rd (and 4th) position, I don't mind whether both (so in total four values) should show up or only three. It's more about the connection, how to get all d1 values, but only the top 3 of the d2 values (for each of the d1's).Markus Popphttps://www.blogger.com/profile/15355530354397508921noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144965859583648702006-04-14T00:04:00.000+02:002006-04-14T00:04:00.000+02:00Just curious Markus, in retrospect, I realize that...Just curious Markus, <BR/><BR/>in retrospect, I realize that you procedure does the same thing as Robin's query (and mine as well), that is: find the three rows that have the highest values - NOT find the three highest values, regardless of the rows that happen to have that value.<BR/><BR/>What was your requirement really? Finding the 3 rows with the highest value, or finding the 3 distinct highest values?rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144944779605257762006-04-13T18:12:00.000+02:002006-04-13T18:12:00.000+02:00Great job, guys :-)!Great job, guys :-)!Markus Popphttps://www.blogger.com/profile/15355530354397508921noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144942708090975582006-04-13T17:38:00.000+02:002006-04-13T17:38:00.000+02:00Could you fix both the suggestions by using >= to ...Could you fix both the suggestions by using >= to compare d2, and add in a t1.id != t2.id?Scott Noyeshttps://www.blogger.com/profile/16530483533864312172noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144942327866722682006-04-13T17:32:00.000+02:002006-04-13T17:32:00.000+02:00LOL! nice one robin...but it has the same flaw as ...LOL! nice one robin...but it has the same flaw as my subquery solution: It returns the three rows that have the highest value - not the three highest values.<BR/><BR/>Of course, your JOIN solution is nicer than my subquery. Props to you!rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144942161328712422006-04-13T17:29:00.000+02:002006-04-13T17:29:00.000+02:00Hi Markus, you want the '...top 3 values..'. Well,...Hi Markus, <BR/><BR/>you want the '...top 3 values..'. Well, if we could assume that (d1, d2) is unique, the solution is simple:<BR/><BR/>select t1.id, t1.d1, t1.d2<BR/>from tt as t1<BR/>where 3 > (<BR/>select count(id)<BR/>from tt as t2<BR/>where t2.d1 = t1.d1<BR/>and t2.d2 > t1.d2<BR/>)<BR/>order by t1.d1, t1.d2 desc<BR/>;<BR/><BR/>+-------+----+--------+<BR/>| id | d1 | d2 |<BR/>+-------+----+--------+<BR/>| 4369 | 1 | 995815 |<BR/>| 6471 | 1 | 994064 |<BR/>| 7358 | 1 | 993948 |<BR/>| 5973 | 2 | 997576 |<BR/>| 9513 | 2 | 992569 |<BR/>| 8241 | 2 | 987857 |<BR/>| 10793 | 3 | 995166 |<BR/>| 9687 | 3 | 992792 |<BR/>| 12900 | 3 | 992669 |<BR/>..<BR/>..<BR/><BR/>Of course, this query only returns the 3 rows with the highest value which is not the same as the three top values (2 or even 3 rows having the highest value might all have the same value)<BR/><BR/>if course, I don't think it's fair to assume unique values in d2 for a given d1. So, I will think a little more and try to come up with a query that literally does what you want.rpboumanhttps://www.blogger.com/profile/13365137747952711328noreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144939008681174752006-04-13T16:36:00.000+02:002006-04-13T16:36:00.000+02:00How about this one:SELECT a.* FROM tt a, tt b WHER...How about this one:<BR/><BR/>SELECT a.* FROM tt a, tt b <BR/>WHERE a.d1 = b.d1 <BR/>GROUP BY a.id <BR/>HAVING SUM(a.d1 = b.d1 AND b.d2 > a.d2) < 3<BR/>ORDER BY a.d1 ASC, a.d2 DESC;<BR/><BR/>Or is there something horrible I'm missing?<BR/><BR/> -robinAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-17155656.post-1144935677553068952006-04-13T15:41:00.000+02:002006-04-13T15:41:00.000+02:00Here's a nasty, ugly solution. I think you could ...Here's a nasty, ugly solution. I think you could improve on the third subquery a bit using count() and a having clause, removing the need for joining inner3.<BR/><BR/>(<BR/> SELECT * <BR/> FROM tt outerTable <BR/> WHERE d2 = (<BR/> SELECT MAX(d2) FROM tt WHERE outerTable.d1 = tt.d1<BR/> )<BR/>)<BR/>UNION (<BR/> SELECT * <BR/> FROM tt outerTable <BR/> WHERE d2 = (<BR/> SELECT max(inner1.d2) <BR/> FROM <BR/> tt inner1 <BR/> JOIN tt inner2 ON (inner1.d1 = inner2.d1 AND inner1.d2 < inner2.d2) <BR/> WHERE inner1.d1 = outerTable.d1<BR/> )<BR/>)<BR/>UNION (<BR/> SELECT * <BR/> FROM tt outerTable <BR/> WHERE d2 = (<BR/> SELECT max(inner1.d2) <BR/> FROM <BR/> tt inner1 <BR/> JOIN tt inner2 ON (inner1.d1 = inner2.d1 AND inner1.d2 < inner2.d2) <BR/> JOIN tt inner3 ON (inner2.d1 = inner3.d1 AND inner2.d2 < inner3.d2) <BR/> WHERE inner1.d1 = outerTable.d1<BR/> )<BR/>)<BR/>ORDER BY d1, d2 DESC;Scott Noyeshttps://www.blogger.com/profile/16530483533864312172noreply@blogger.com