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.
MySQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Kanti Prasad

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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.
Mike Eghtebas

;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

movieprodw

ASKER
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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Mike Eghtebas

Well, because you never indicated it is MySql. cte is good in SQL Server not MySQL. I am trying to code differently now.
movieprodw

ASKER
I am so sorry
Mike Eghtebas

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
movieprodw

ASKER
Mike Eghtebas

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

movieprodw

ASKER
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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Mike Eghtebas

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PortletPaul

#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