?

Log in

No account? Create an account

Previous Entry Share Next Entry
Two words: DU... HUUUDE!
digimind
thwack
Astronomy Picture of the Day: Hurricane Isabel Approaches
(click the picture for really high resolution)

In other news, I'm trying to learn some SQL to prune a high scores database. So far I've accomplished this:

mysql> SELECT *, bubbles/seconds AS bps FROM scoresold WHERE name =
 "Thwack" ORDER BY mbubbles, bubbles, bps DESC;
+--------+---------+---------+----------+----------------+--------+
| name   | seconds | bubbles | mbubbles | datetime       | bps    |
+--------+---------+---------+----------+----------------+--------+
| Thwack |    18.2 |      77 |        0 | 20030915012150 |  4.231 |
| Thwack |   153.5 |     506 |        0 | 20030915013048 |  3.296 |
| Thwack |     2.6 |      77 |       77 | 20030915033829 | 29.615 |
| Thwack |     3.5 |      77 |       77 | 20030915013318 | 22.000 |
| Thwack |     3.6 |      77 |       77 | 20030915013137 | 21.389 |
| Thwack |     9.0 |      77 |       77 | 20030915005639 |  8.556 |
| Thwack |     8.1 |     196 |      196 | 20030915014001 | 24.198 |
| Thwack |    11.7 |     196 |      196 | 20030915003715 | 16.752 |
| Thwack |    26.5 |     196 |      196 | 20030915005719 |  7.396 |
+--------+---------+---------+----------+----------------+--------+


But what I need is for it to only return the row for the highest bps for each unique combination of bubbles and mbubbles. Or the opposite.

Since I want to keep only the highest bps for each of the 6 ways to play (bubbles has 3 values and mbubbles has 2: bubbles or 0), I need to write either a SELECT query that only returns the highest bps for each so I can write them out to another table, or a DELETE query that only returns the non-highest bps for each so I can delete those to leave the highest scores.

But then here's the other requirement: it needs to do that for every possible "name" in the table. "Thwack" is just one of many. Some of them have over 100 rows, which is why I want to prune them.

Not having much luck here. :-/

  • 1
Yeh, you're right about the randomness. I forgot about that when I wrote it.

The group by method returns aggregate data to work on. Anything that isn't part of the group by or part of an aggregate fuction is pretty much random.

Okay, another keyword... lol

SELECT *, MAX(bubbles/seconds) AS bps FROM scores2 WHERE bubbles = 77 AND mbubbles = 77 GROUP BY name HAVING (bubbles/seconds) = MAX(bubbles/seconds);

See if that does it. You don't have to specify the MAX column if you don't want to, just put star. I put it there so you could debug. ;-)

For some reason that only returned 9 rows, while there are 21 distinct names where bubbles=77 and mbubbles = 77.

hmm weird. Are you looking at the right table? I used scores2 in that example.

Yup.

mysql> SELECT DISTINCT NAME FROM scores2 WHERE bubbles = 77 AND mbubbles = 77;
[...]
21 rows in set (0.00 sec)

I renamed scoresold to scores2 after my initial post.

Okay, maybe there's something weird going on like none of the bps match the max for certain users...

maybe rounding errors...

  • 1