How to delete row if the previous have the same value in specific a column

Question

Let's say I have a table that looks something like this:

-------------------------------
id|column2|column3   |column4
-------------------------------
1  value1  somevalue  somevalue
2  value2  somevalue  somevalue
3  value2  somevalue  somevalue <----- I want this column deleted
4  value3  somevalue  somevalue

As you can see I want to delete the rows where the current and the previous row have the same value in column2.

Answer 1

I would try something like

DELETE FROM Table t1 
WHERE EXISTS (SELECT 1 from Table t2 
              WHERE t1.Column2 = t2.Column2 
              AND t2.Id + 1 = t1.Id) 

For your specific query, I would use the t2.Id + 1 = t1.Id instead of t2.Id < t1.Id as this would delete for any occurances, not consecutive entries only.

Answer 2

DELETE FROM Table t1
WHERE EXISTS (SELECT 1 from Table t2
              WHERE t1.Column2 = t2.Column2
              AND t1.Column3 = t2.Column3                  
              AND t1.Column4 = t2.Column4
              AND t2.Id > t1.Id)

OR if you need compare rows just by one field

DELETE FROM Table t1
WHERE EXISTS (SELECT 1 from Table t2
              WHERE t1.Column2 = t2.Column2
              AND t2.Id > t1.Id)