[MySQL] Delete duplicate entries with less score *SOLVED*

[benny]

Hi.

I have a MySQL question. Maybe someone has can help me here.

Let's say we have the following table

Code: Select all

[b]highscore[/b]

+--------+-------+
| name  | score |
+--------+-------+
| bob     | 10     |
| bob     | 20     |
| anne   | 13     |
| anne   | 15     |
+--------+-------+

Is there a single SQL-Statement that deletes all duplicate entry concerning
the name where the score is less the maximum score.

So, that the remaining table would look like this :

Code: Select all

[b]highscore[/b]

+--------+-------+
| name  | score |
+--------+-------+
| bob     | 20     |
| anne   | 15     |
+--------+-------+

I would think about using PHP with getting all the different names using
the DISTINCT command and then gettint the maximum score
of each username and then delete all entries of the user with less score.

Nevertheless, I asked myself if this could be handled by just one clever
SQL-Statement

Any help appreciated.

[traumatic]

Hi benny,

hmm... like this?

Code: Select all

DELETE highscore FROM highscore t1, highscore t2
WHERE t1.score < t2.score AND t1.name = t2.name

However you shouldn't run into this problem at all.

Firstly I'd check if the new highscore is higher than the one
already present. Then, depending on the result, I'd decide
whether to make an UPDATE or INSERT.

That would make things much less complicated IMHO.

[benny]

@traumatic:

Thanks. I'll try this.

And you are of course right. Problem is that I have to fix an online game
with a giving database. So, all there are already so many entries in that
table that I have to "clean" it one time without deleting the highest scores.

Then, of course, I will check it each time before writing new highscores...

[traumatic]

Ok, got it!

Be sure to let me know if the query worked for you.

[benny]

I am trying your statement with SELECT instead of DELETE to
be on the safe side before I delete something I dont want to delete.

The following didnt work :

Code: Select all

SELECT playerscore FROM tx_r21memgame_highscore  t1, tx_r21memgame_highscore t2
WHERE t1.playerscore < t2.playerscore AND t1.playername = t2.playername AND
t1.playername = '83423'

But if I change "playerscore" to "*" I got all entries with less score. So it seems
to work. Thanks a lot !!!

[traumatic]

Always do a backup beforehand!


Glad it seems to work. You're welcome, Benny.

[benny]

Yup. I dumped the table before. Nevertheless. I am not very safe when it
comes to databases and sql to be honest. Thats why I am double careful
right now.

Thanks a lot again. Traumatic. And of course greetings, mate!

[benny]

Ok. Just to finish this thread.

I successfully deleted all the entries as I intended to do ... What traumatic
posted in his first thread did work for 100%. Didn't has to change any line

So thanks again mate.

Problem solved. /me happy

[traumatic]

Cool!
/me too