?

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
how do you know what the playstyle is? I see only 4 unique sets of bubbles/mbubles ( games? ).

If each one is number is tied to a way to play:

SELECT *, MAX(bubbles/seconds) AS bps FROM scoresold GROUP BY name where bubbles = 77 and mbubbles = 0;

( I think i've done this right, you'll have to test it out )
You'd have to do that for each type of game. But the "group by" keyword might be what you're looking for.

Yeah, I tried GROUP BY many different ways but none of them do what I expect.

There are only 4 unique sets of bubbles/mbubbles for the name "Thwack" because I haven't played two of the possible sets. A player can select a number of bubbles (77, 196 or 506) and can either pop them all in "manic" mode or manually. mbubbles is a count of the ones popped in manic mode, which can only be 0 or equal to bubbles for submitted scores.

WHERE cannot appear after GROUP BY. Also, I only played one 7/0 game, so I tried your suggestion as follows:

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

It returned only one row for each name with that set of bubbles/mbubbles, but while the value shown in the bps column is the MAX for that name, the rest of the row does not match the row where that bps was found.

The row for Thwack from that query is:

| Thwack | 9.0 | 77 | 77 | 20030915005639 | 29.615 |

But if I query for ALL rows where name="Thwack" and bubbles=77 and mbubbles=77, the row with the highest bps is:

| Thwack | 2.6 | 77 | 77 | 20030915033829 | 29.615 |

And in fact there is no such row as the one displayed by the first query, which seems to just grab the rest of the columns from a random row (probably the first one in the unsorted table). I could safely assume that MIN(seconds) would give me the correct value for that column, but there's no way to find the correct datetime.

Plus I don't want the bps column to be shown, since I would be writing the resulting rows to a new table, and it's a waste of space to store a calculated column.

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

As a side note, every time I read mbubbles I see "my bubbles" and conjure up a clip of Finding Nemo.

My bubbles, my bubbles!

  • 1