SQL to remove rows with duplicated value while keeping one

Question

Say I have this table

id | data | value ----------------- 1 | a | A 2 | a | A 3 | a | A 4 | a | B 5 | b | C 6 | c | A 7 | c | C 8 | c | C

I want to remove those rows with duplicated value for each data while keeping the one with the min id, e.g. the result will be

id | data | value ----------------- 1 | a | A 4 | a | B 5 | b | C 6 | c | A 7 | c | C

I know a way to do it is to do a union like:

SELECT 1 [id], 'a' [data], 'A' [value] INTO #test UNION SELECT 2, 'a', 'A' UNION SELECT 3, 'a', 'A' UNION SELECT 4, 'a', 'B' UNION SELECT 5, 'b', 'C' UNION SELECT 6, 'c', 'A' UNION SELECT 7, 'c', 'C' UNION SELECT 8, 'c', 'C' SELECT * FROM #test WHERE id NOT IN ( SELECT MIN(id) FROM #test GROUP BY [data], [value] HAVING COUNT(1) > 1 UNION SELECT MIN(id) FROM #test GROUP BY [data], [value] HAVING COUNT(1) <= 1 )

but this solution has to repeat the same group by twice (consider the real case is a massive group by with > 20 columns)

I would prefer a simpler answer with less code as oppose to complex ones. Is there any more concise way to code this?

Thank you

Raging Bull · Accepted Answer · 2021-10-13 11:00:16Z

You can use one of the methods below:

Using WITH CTE:

 WITH CTE AS (SELECT *,RN=ROW_NUMBER() OVER(PARTITION BY data,value ORDER BY id) FROM TableName) DELETE FROM CTE WHERE RN>1

Explanation:

This query will select the contents of the table along with a row number RN. And then delete the records with RN >1 (which would be the duplicates).

This Fiddle shows the records which are going to be deleted using this method.

Using NOT IN:

 DELETE FROM TableName WHERE id NOT IN (SELECT MIN(id) as id FROM TableName GROUP BY data,value)

Explanation:

With the given example, inner query will return ids (1,6,4,5,7). The outer query will delete records from table whose id NOT IN (1,6,4,5,7).

This fiddle shows the records which are going to be deleted using this method.

Suggestion: Use the first method since it is faster than the latter. Also, it manages to keep only one record if id field is also duplicated for the same data and value.

Note that this does not work if your data are totally duplicate. The only difference will then be the row number, it should be used to select only the row with the lowest number of the group.
@EricBurel: If all fields are duplicated, the first solution (one with CTE) can be used after adding id field also in the partition by columns.
I've found another solution by using physical row id in Postgres, more broadly some special fields specific to a DBMS may help differentiate otherwise identical rows but yes adding an id manually in a first query should indeed.

Choussamaster · Accepted Answer · 2019-02-27 11:01:15Z

I want to add MYSQL solution for this query

Suggestion 1 : MySQL prior to version 8.0 doesn't support the WITH clause

Suggestion 2 : throw this error (you can't specify table TableName for update in FROM clause

So the solution will be

DELETE FROM TableName WHERE id NOT IN (SELECT MIN(id) as id FROM (select * from TableName) as t1 GROUP BY data,value) as t2;

Collectives™ on Stack Overflow

SQL to remove rows with duplicated value while keeping one

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related