January 26th, 2013, 05:08 PM
Join Date: Mar 2012
Time spent in forums: 2 h 31 m 12 sec
Reputation 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!