;with
cte
As
( Select row_number() Over(partition by sku, qty, [date] order by [date]) As rn
,sku,qty,[date]
From #Table1
)
Delete from cte
Where rn>1;
Select * From #Table1
Starting with 17 rows, this code determines 5 repeat rows and deletes them leaving 12 behind. Please change #Table1 to your table name. Hear is my temp table #Table1 I have tested the solution with:
create table #Table1(sku int, qty int, [date] varchar(20));
insert #Table1(sku,qty,[date]) values
(111,4,'2015-07-23 04:59:51')
,(111,4,'2015-07-23 04:59:51')
,(113,2,'2015-07-23 04:59:51')
,(113,2,'2015-07-23 04:59:51')
,(113,2,'2015-07-23 04:59:51')
,(116,8,'2015-07-23 04:59:51')
,(117,3,'2015-07-23 04:59:51')
,(111,4,'2015-08-23 04:59:51')
,(113,2,'2015-08-23 04:59:51')
,(113,2,'2015-08-23 04:59:51')
,(116,8,'2015-08-23 04:59:51')
,(117,3,'2015-08-23 04:59:51')
,(111,4,'2015-09-23 04:59:51')
,(113,2,'2015-09-23 04:59:51')
,(116,8,'2015-09-23 04:59:51')
,(116,8,'2015-09-23 04:59:51')
,(117,3,'2015-09-23 04:59:51');
;with
cte
As
( Select row_number() Over(partition by sku, qty, log_time order by log_time) As rn
,sku,qty,log_time
From stock_tracker_copy
)
Delete from cte
Where rn>1;
Delete d
From
(Select row_number() Over(partition by sku, qty, [date] order by [date]) As rn
,sku,qty,[date]
From #Table1) d
Where rn>1;
Delete d
From
(Select row_number() Over(partition by sku, qty, [date] order by [date]) As rn
,sku,qty,[date]
From #Table1) d
Where rn>1;
Delete d
From
(Select row_number() Over(partition by sku, qty, log_time order by log_time) As rn
,sku,qty,log_time
From stock_tracker_copy) d
Where rn>1;
Error : 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 '(partition by sku, qty, log_time order by log_time) As rn
Thank you for the response.
I was hoping that I could just remove them from the current table with one quick script.
Is there a way to do it where we could pull the sku per day, then delete the ones that are not END()?
Hope that makes sense, I know I have done it in the past but it was years ago.