#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    3
    Rep Power
    0

    Effecient Query needed to remove duplicates


    Hi folks and thanks for reading

    I have a quiz feature on my site which stores a score, username and ip address as the most important columns. I currently have a horrible series of views bringing back the high scores based on the criteria I need which are...

    Lowest score first but...only the lowest score for each user.

    The complexity lies if the user has changed ip, i.e. keeps the same username but has a different ip

    It's easier to give you an example

    UserID IPA Score
    User 1 IP1 13 |
    User 1 IP1 20 | First user but from
    User 1 IP2 30 | 3 different IP Addresses
    User 1 IP3 10 |

    User 2 IP4 20 |
    User 2 IP5 22 | Second user from 2 IP Addresses
    User 2 IP5 15 |

    User 3 IP6 12 |
    User 3 IP6 20 | Third user using one IP Address
    User 4 IP6 15 | but using 3 Usernames
    User 5 IP6 11 |


    The highscore query would present you with

    User 1 IP3 10
    User 5 IP6 11
    User 2 IP5 15

    The score value is highly unlikely to be duplicated but I guess it is possible. The figures above are simplified to explain my conundrum!

    Can anyone suggest an efficient way of removing these duplicates as my table is now over 15,000 records and my Views are creaking!

    Many thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,279
    Rep Power
    4279
    15,000 rows is a small table

    why do you feel the need to remove data?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    3
    Rep Power
    0
    Sorry for not being clear. Not physically deleting the duplicates, just returning the rows I want (either as a direct query from the table or as a view).

    My current setup uses several views to narrow the results down ensuring that a "visitor" only has one result returned in the High Score query, even if they have changed IP address or Username.

    Hopefully the example is a clear enough indication of the result query I'm trying to obtain.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,279
    Rep Power
    4279
    Originally Posted by Ceeege
    ... even if they have changed IP address or Username.
    i think you're gonna have a real problem implementing this particular requirement

    if a user can change both his name and his ip, you're not going to able to tell users apart at all
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo