Link to home
Start Free TrialLog in
Avatar of movieprodw
movieprodw

asked on

SQL Remove Duplicates on that day

Hello,

I have a table with a bunch of rows, they have sku|qty|date, the issue is that I have duplicate rows in some of the days and I need to remove those as they are exactly the same for that day.

So I need to run a sql query that would keep one and delete the duplicate.

Table,
sku,qty,date
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

Any help would be greatly appreciated.
ASKER CERTIFIED SOLUTION
Avatar of Kanti Prasad
Kanti Prasad

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of movieprodw
movieprodw

ASKER

Hello,

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.
;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

Open in new window

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');

Open in new window

I am sorry I wrote date as the column name I should have written the correct one which is log_time.

I have tried your query and the error I am getting is:
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 'with

;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;

Open in new window

Well, because you never indicated it is MySql. cte is good in SQL Server not MySQL. I am trying to code differently now.
I am so sorry
try:
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;

Open in new window

movieprodw,

Please see my second solution without cte. BTW, with the new solution, I am using derived table which is good in MySQL. However, I have kept Partition By from the last solution and you need to give it a try to see if Partition BY works in MySQL. Please let me know after you try:
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;

Open in new window

Hi eghtebas,

I have ran this
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;

Open in new window


and receive this error
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

Open in new window

movieprodw,

I am not sure how to apply partition by in MySQL.

Suggestion:
1. Use the solution from Kanti Prasad, or
2. Wait for a different solution by another expert.
3. Delete question and post it again (This time indicate MySQL).

Good luck,

Mike
Avatar of PortletPaul
Row_number() function is not available in MySQL (so far)

Please refer to this previous answer:
https://www.experts-exchange.com/questions/28442117/Remove-duplicates-but-keep-one-for-combination-of-two-columns-in-MySQL.html

In that approach you create a table id ID's that you DO want to retain, then delete all the others
I've requested that this question be closed as follows:

Accepted answer: 0 points for movieprodw's comment #a40898568
Assisted answer: 500 points for Kanti Prasad's comment #a40898552

for the following reason:

Thank you both for your help.
#a40898552 is not a solution at all (it's a note about a mistake and an error message)

kantiprasad's comment #a40898552  should simply be chosen as the accepted answer