Dan Price (thwack) wrote,
Dan Price
thwack

  • Mood:

Two words: DU... HUUUDE!

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. :-/
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 9 comments