?

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

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

No HTML allowed in subject

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

Notice! This user has turned on the option that logs IP addresses of anonymous posters. 

(will be screened)