I like CTEs and ROW_NUMBER as the two combined allow us to see which rows are deleted (or updated), therefore just change the DELETE FROM CTE… to SELECT * FROM CTE: WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM … Read more
The key is to rewrite this query so that it can be used as a subquery. SELECT firstname, lastname, list.address FROM list INNER JOIN (SELECT address FROM list GROUP BY address HAVING COUNT(id) > 1) dup ON list.address = dup.address;
How do I find the duplicates in a list and create another list with them?
For people who have come here to look for a general answer for duplicate row removal, use !duplicated(): a <- c(rep(“A”, 3), rep(“B”, 3), rep(“C”,2)) b <- c(1,1,2,4,1,1,2,2) df <-data.frame(a,b) duplicated(df) [1] FALSE TRUE FALSE FALSE FALSE TRUE FALSE TRUE > df[duplicated(df), ] a b 2 A 1 6 B 1 8 C 2 > … Read more
SELECT DISTINCT a,b,c FROM t is roughly equivalent to: SELECT a,b,c FROM t GROUP BY a,b,c It’s a good idea to get used to the GROUP BY syntax, as it’s more powerful. For your query, I’d do it like this: UPDATE sales SET status=”ACTIVE” WHERE id IN ( SELECT id FROM sales S INNER JOIN … Read more
Finding duplicate values in a SQL table
duplicated has a fromLast argument. The “Example” section of ?duplicated shows you how to use it. Just call duplicated twice, once with fromLast=FALSE and once with fromLast=TRUE and take the rows where either are TRUE. Some late Edit: You didn’t provide a reproducible example, so here’s an illustration kindly contributed by @jbaums vec <- c(“a”, … Read more
This is much easier in pandas now with drop_duplicates and the keep parameter. import pandas as pd df = pd.DataFrame({“A”:[“foo”, “foo”, “foo”, “bar”], “B”:[0,1,1,1], “C”:[“A”,”A”,”B”,”A”]}) df.drop_duplicates(subset=[‘A’, ‘C’], keep=False)
Editor warning: This solution is computationally inefficient and may bring down your connection for a large table. NB – You need to do this first on a test copy of your table! When I did it, I found that unless I also included AND n1.id <> n2.id, it deleted every row in the table. If … Read more
How do I remove repeated elements from ArrayList?