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.
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
Last Comment
PortletPaul
ASKER CERTIFIED SOLUTION
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.
;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
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');
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
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;
Well, because you never indicated it is MySql. cte is good in SQL Server not MySQL. I am trying to code differently now.
ASKER
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;
ASKER
Please see screenshot
Screen-Shot-2015-07-25-at-11.22.36-AM.jp
Screen-Shot-2015-07-25-at-11.22.36-AM.jp
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:
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;
ASKER
Hi eghtebas,
I have ran this
and receive this error
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;
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
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
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
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
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
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.
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
kantiprasad's comment #a40898552 should simply be chosen as the accepted answer
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.