This blog post has moved. Please find it at:
http://www.mpopp.net/2006/04/a-little-brain-exercise/.
Subscribe to:
Post Comments (Atom)
skip to main |
skip to sidebar
This is all about db4free.net and the database systems that it offers. The main focus are practical examples of the new features of MySQL 5 and 6 that should show you how to use them for your advantage in your daily life ;-).
About Me
Links
Blog Archive
-
▼
2006
(124)
-
▼
April
(12)
- Returning home from the MySQL UC
- db4free.net offers MySQL 5.1.10
- The countdown is running
- My first live webinar experience
- Something broken at the development source tree?
- Alternative privilege system including ROLES and s...
- A little (?) brain exercise
- User XYZ != User XYZ
- Events work much better now
- The downside of information_schema
- QueryBrowser 1.2.7?
- Woooohoooooo - the 21st century has just begun!
-
▼
April
(12)
7 comments:
How about this one:
SELECT a.* FROM tt a, tt b
WHERE a.d1 = b.d1
GROUP BY a.id
HAVING SUM(a.d1 = b.d1 AND b.d2 > a.d2) < 3
ORDER BY a.d1 ASC, a.d2 DESC;
Or is there something horrible I'm missing?
-robin
Hi Markus,
you want the '...top 3 values..'. Well, if we could assume that (d1, d2) is unique, the solution is simple:
select t1.id, t1.d1, t1.d2
from tt as t1
where 3 > (
select count(id)
from tt as t2
where t2.d1 = t1.d1
and t2.d2 > t1.d2
)
order by t1.d1, t1.d2 desc
;
+-------+----+--------+
| id | d1 | d2 |
+-------+----+--------+
| 4369 | 1 | 995815 |
| 6471 | 1 | 994064 |
| 7358 | 1 | 993948 |
| 5973 | 2 | 997576 |
| 9513 | 2 | 992569 |
| 8241 | 2 | 987857 |
| 10793 | 3 | 995166 |
| 9687 | 3 | 992792 |
| 12900 | 3 | 992669 |
..
..
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)
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.
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.
Of course, your JOIN solution is nicer than my subquery. Props to you!
Great job, guys :-)!
Just curious Markus,
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.
What was your requirement really? Finding the 3 rows with the highest value, or finding the 3 distinct highest values?
Hi Roland,
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).
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).
Probably not for this problem but a logical solution for a mystical problem in mysql older versions..
http://formyhelp.blogspot.com/2008/03/error1235-this-version-of-mysql-doesnt.html
Post a Comment