1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to delete duplicate records?

Discussion in 'Databases' started by nsharony, Jun 16, 2017.

  1. #1
    Hi,

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

    I have a table like so:
    [​IMG]
    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)
    SEMrush
    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:
    [​IMG]
    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:
    [​IMG]
    Does anyone know what is the correct way of doing this?
     
    nsharony, Jun 16, 2017 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,625
    Likes Received:
    719
    Best Answers:
    151
    Trophy Points:
    420
    #2
    I think DISTINCT can do this for you - SELECT DISTINCT user, COUNT(*) FROM table or similar.
     
    PoPSiCLe, Jun 17, 2017 IP
  3. K Padmapriya

    K Padmapriya Member

    Messages:
    53
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    38
    #3
    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);
     
    K Padmapriya, Nov 6, 2017 IP