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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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.
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
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');
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.
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;
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;
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:
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
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial