tag:blogger.com,1999:blog-17155656.post114488102840870698..comments2023-04-06T12:09:13.895+02:00Comments on db4free.net blog: A little (?) brain exerciseMarkus Popphttp://www.blogger.com/profile/15355530354397508921noreply@blogger.comBlogger7125tag: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-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.com