January 26th, 2013, 05:08 PM
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!
January 26th, 2013, 07:13 PM
15,000 rows is a small table
why do you feel the need to remove data?
January 26th, 2013, 07:23 PM
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.
January 26th, 2013, 07:35 PM
i think you're gonna have a real problem implementing this particular requirement
Originally Posted by Ceeege
if a user can change both his name and his ip, you're not going to able to tell users apart at all