1

I have a table in my DB where I keep track of user data. So I have rows like that:

(row_id,user_id, stat1,stat2,..., statN, timestamp)

Now I want to remove all duplicate records, where user_id and stat columns are the same, but only between other rows with different data. So if I have

(row_id, user_id, stat1, stat2, stat3, timestamp)
(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(3,1,1,1,1,3000)
(4,1,1,0,1,4000)

It should became

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)
(4,1,1,0,1,4000)

And not

(1,1,1,0,1,1000)
(2,1,1,1,1,2000)

So I want to save record progression but remove meaningless duplicates.

1
  • Marco, thank you for markup, first time asking question here and from mobile :)
    – BlackSwan
    Sep 4, 2018 at 8:34

2 Answers 2

0
delete t1.*
from `table` t1
inner join `table` t2
  on  t1.user_id = t2.user_id 
  and t1.stat1=t2.stat1
  and t1.stat2=t2.stat2
      /* ........... */  
  and t1.statN=t2.statN
  and t1.timestamp>t2.timestamp
left join `table` t3
   on t1.user_id = t3.user_id 
  and t3.timestamp > t2.timestamp 
  and t3.timestamp < t1.timestamp
where t3.user_id is null;

fiddle

7
  • Yay! Looks like exactly what I need! Will test it in few hours. Than you very much.
    – BlackSwan
    Sep 4, 2018 at 9:00
  • @BlackSwan There can be a problem if the combination (user_id,timestamp) is NOT unique.
    – Akina
    Sep 4, 2018 at 9:01
  • It should not be, but I will check for it, thanks
    – BlackSwan
    Sep 4, 2018 at 9:04
  • @BlackSwan I do not know your task, but I think the uniqie index by the expression above may be usable... not only the check/deletion you need now. On the other hand it can affect on some soft causing errors, so be carefully.
    – Akina
    Sep 4, 2018 at 9:06
  • Hm... Just put it in to phpMyAdmin on duplicate table of data set, it sez Error #1093, Table that changing t1 cannot be used in FROM
    – BlackSwan
    Sep 4, 2018 at 9:20
0

So doing a join on the elements that constitute a duplicate and the last where criteria shows which to delete.

delete t1
from table t1, table t2
where t1.user_id = t2.user_id and t1.stat1=t2.stat1
     and t1.stat2=t2.stat2  and t1.stat3=t2.stat3
and t1.row_id+1=t2.row_id
7
  • Isn't it going to transform data in that way?(Which is unwanted behaviour) (row_id, user_id, stat1, stat2, stat3, timestamp) (1,1,1,0,1,1000) (2,1,1,1,1,2000) (3,1,1,1,1,3000) (4,1,1,0,1,4000) \/ (1,1,1,0,1,1000) (2,1,1,1,1,2000)
    – BlackSwan
    Sep 4, 2018 at 8:45
  • why would it? Delete can only remove rows, it can't transform. Try on a test dataset to confirm this outcome.
    – danblack
    Sep 4, 2018 at 8:46
  • I can't test it right now, but I believe you are missing the point of question. Look again at the question, please. I afraid that request will return result as in unwanted case with only two rows survive deletion. I need preserve duplicates that are separated from each other by rows that are different
    – BlackSwan
    Sep 4, 2018 at 8:50
  • So and t1.row_id+1=t2.row_id as the final part of the query would solve this?
    – danblack
    Sep 4, 2018 at 8:54
  • @danblack No guarantee of holes in row_id. Moreover, I cannot see guarantee of (t1.timestamp>t2.timestamp) => (t1.row_id>t2.row_id).
    – Akina
    Sep 4, 2018 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.