SQL Remove Duplicates on that day

movieprodw
movieprodw used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015
Commented:
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

Author

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 Developer

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

CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Author

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 Developer

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

Author

Commented:
I am so sorry
Mike EghtebasDatabase and Application Developer

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

Author

Commented:
Mike EghtebasDatabase and Application Developer

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

Author

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 Developer

Commented:
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 Advisor
Most Valuable Expert 2014
Awarded 2013

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

Author

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 Advisor
Most Valuable Expert 2014
Awarded 2013

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial