0

I usually do this, but later i found this there were an error by doing this:

DELETE FROM pms_log_sms
WHERE id NOT IN (
              SELECT DISTINCT id FROM (
              SELECT  MAX(id) AS id FROM pms_log_sms
              GROUP BY username,`time`,text
              HAVING COUNT(*) > 0 )   A     
        );

my table have :

unique id (autoincre), username, text, time

text is encoded for example:

record 1: 
//FuxOBtL8Lmop0Az943oBNWSTvUYSk8+euJWKgMJHk=

and record 2:
//FuxOBtL8Lmop0Az943oDB+aoIMJXdTltYQJ1j0TbRARwwCIZiwIwovq8LrgYtV

the queries will found these as duplicate and delete them but they aren't same text, sometime they post at the same time, but diff unique id, same username but the query will found these as duplicate data and delete them but they aren't same text

any solution for this ?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Jimmy Le
  • 69
  • 1
  • 9
  • 1
    When trying to delete duplicate rows of a single table, why do you delete from `log_text` but select from `pms_log_sms`? Also, did you have a look at the other questions about deleting duplicate rows? Why is your request different? – Peter Lang Apr 27 '14 at 09:45
  • 1
    Your DB schema shouldn't allow for duplicate rows. Check your indices. – DanMan Apr 27 '14 at 11:27
  • 1
    possible duplicate of [Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) – Peter Lang Apr 27 '14 at 12:07
  • sorry, its table pms_log_sms, not log_text. The thing is i can delete duplicate rows, but also include those rows are not duplicate like sample above, it delete record 1 and record 2 of text column when they are clearly two difference record – Jimmy Le Apr 27 '14 at 12:30
  • `having count(*) > 0` doesn't do anything in this context. – Gordon Linoff Apr 27 '14 at 12:33
  • having count (*) > 0 , it only delete duplicate row that count more than 0, and keep 1 unique i think – Jimmy Le Apr 27 '14 at 13:05

0 Answers0