How to delete duplicate records?

Hi,

I've been struggling with this problem and hopefully someone could point me in the right direction.

I have a table like so:

Each row represents a user who has a mobile device with an install game.
Row 1 says that user 1 has device abc and with game 1 installed on it.
The same user has game 2 installed on the same device (as seen by row 2)

What I need to do is to identify all the duplicate and to delete them so that at the end,
any device that has more than 1 game installed by the same user will be delete.

This query gives me the breakdown of the number of duplicates per user and game:

select game, user, count(*) from `data` group by 1,2

Code (markup):

This gives this result:

This means that user 1 has game 1 installed on two devices and also game 2 installed on two devices.

What I would like to do is to delete all the records that cause the count(*) in the group by to return any value greater than 1.
The end result should be being left with just these records:

Does anyone know what is the correct way of doing this?

 

I think DISTINCT can do this for you - SELECT DISTINCT user, COUNT(*) FROM table or similar.

 

For deleting a record ,
SQL>DELETEFROM table_name A WHERE ROWID> ( 2 SELECT min(rowid) FROM table_name B 3 WHERE A.Key_values=B.key_values);