Dan Price

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. :-/