120 reputation
4
bio website
location
age
visits member for 1 year, 9 months
seen Mar 14 at 6:41

stack overflow rulez! has helped me many times in the past so I am here to give a little back and to learn. Answering questions here is a great way to learn.


Oct
25
comment remove almost duplicates based on insert time
I ended up using 5 minutes (300 seconds) as the boundary, tested on a clone of the live table and it works great thanks. Switched live.
Oct
25
accepted remove almost duplicates based on insert time
Oct
25
comment remove almost duplicates based on insert time
OK I just checked my upload script and because there are few insertions/batch, maybe 50 tops, and some data juggling involved, it is done row by row so ignore will work for this. :D you get the accepted answer. Thanks for the insights.
Oct
25
comment remove almost duplicates based on insert time
I like the table boundary approach, it has elegance, but it gives me a new problem in that the insertions are done by batch upload and if I use this it rejects both rows of a pair of duplicates. If I add ignore then both get inserted. But this approach is VERY promising.
Oct
24
comment remove almost duplicates based on insert time
hmm even then it is returning too many rows. I'll keep at it with variations. I don't quite follow the logic but I get the concept. This of course leads me to make wild guesses.
Oct
24
comment remove almost duplicates based on insert time
your ten second boundary added to the table is an interesting approach. I will look into it further to see if it suits my needs. I do like the approach of filtering the entries on input. Will get back to you.
Oct
24
comment remove almost duplicates based on insert time
I read on dev.mysql that mysql may not like var names that are the same as column names so I changed your query as follows: @swipe_time->@swipe_time3 swipe_time2->@swipe_time2 and got good results :D if you update your answer I'll ...
Oct
24
awarded  Commentator
Oct
24
comment remove almost duplicates based on insert time
gives: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= IF(@g=personalid AND TIMESTAMPDIFF(SECOND, @swi' at line 4
Oct
24
comment remove almost duplicates based on insert time
looks promising after changing := to = to make mysql happy and but I'm not sure what 'tbl' refers to in the 4th last line. I tried swipes there but then it complains 'Every derived table must have its own alias' so I added ZZ before the comma before where tbl was and now it gives "Unknown column 'swipe_time2' in 'field list'" so I'm a bit lost.
Oct
24
comment remove almost duplicates based on insert time
your initial query when filtered that way returns rows with no duplicate
Oct
24
comment remove almost duplicates based on insert time
indeed! so I guess it has to be 0,12,24,36,48...
Oct
24
comment remove almost duplicates based on insert time
thanks, I really appreciate the time you have put into this however your answer does not address the time range aspect or filter out rows with ride_taken=0 or status=0. Suggestion two is appreciated but does not apply to this situation where source number will always be <10. Also I don't understand why a second Unique key identical to the existing one will help accommodate the query. Suggestion 3 does not apply here, multiple swipes in this case are false entries, which is of course why I am trying to filter them out. but +1 for your attempt
Oct
24
comment remove almost duplicates based on insert time
That is an excellent question +1. What I want is for there to be max 1 entry for any unique (personalid, ride_taken=1) within the time range, so for your example I would take entries starting at 0 seconds: 0,12,21,30,42,...
Oct
24
asked remove almost duplicates based on insert time
Oct
24
comment Several variants of a string in tables
ahh my bad, to correct what I said earlier, it's the part after the scheme and host that is case sensitive with Apache, so example.com/thisOne can be different from example.com/thisone but not apparently in your case.
Oct
24
comment Several variants of a string in tables
then clearly you do not need to store both :)
Oct
23
comment Several variants of a string in tables
one thing to keep in mind in regards storing both upper and lower case variants will be dependent on the web server type. For instance Apache servers in general are case sensitive so example.com is a different URL to Example.com
Oct
23
awarded  Scholar
Oct
23
awarded  Supporter