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.
LVL 1
movieprodwAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kanti PrasadCommented:
Hi  

identify which rows have duplicate primary key values
           SELECT sku, qty, date, count(*) FROM t1 GROUP BY sku, qty, date HAVING count(*) > 1

Select the duplicate key values into a new holding table1.
           SELECT sku, qty, date, count(*)  INTO NewTable1 FROM t1 GROUP BY sku, qty, date HAVING count(*) > 1


Select the duplicate rows into a new holding table2
          SELECT DISTINCT t1.*  INTO NewTable2 FROM t1, NewTable1 WHERE t1.sku = NewTable1.sku AND t1.qty = NewTable1.qty
              AND t1.date = NewTable1.date

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
movieprodwAuthor Commented:
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 EghtebasDatabase and Application DeveloperCommented: 
;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

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
movieprodwAuthor Commented:
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

Mike EghtebasDatabase and Application DeveloperCommented:
Well, because you never indicated it is MySql. cte is good in SQL Server not MySQL. I am trying to code differently now.
movieprodwAuthor Commented:
I am so sorry
Mike EghtebasDatabase and Application DeveloperCommented:
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

movieprodwAuthor Commented:
Mike EghtebasDatabase and Application DeveloperCommented:
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

movieprodwAuthor Commented:
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

Mike EghtebasDatabase and Application DeveloperCommented:
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
PortletPaulEE Topic AdvisorCommented:
Row_number() function is not available in MySQL (so far)

Please refer to this previous answer:
http://www.experts-exchange.com/Database/MySQL/Q_28442117.html

In that approach you create a table id ID's that you DO want to retain, then delete all the others
movieprodwAuthor Commented:
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.
PortletPaulEE Topic AdvisorCommented:
#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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.