SQL to delete rows with duplicate value 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 these lines with a duplicate value for each information, keeping one of them with a minimum id, for example. 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 this to make 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 should repeat the same group twice (consider that the real case is a massive group with> 20 columns)

I would prefer a simpler answer with less code than against complex ones. Is there an easier way to code this?

thank

Answer 1

Two alternatives:

1. Usage 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:

   RN. RN > 1 ( ).

   , .

2. NOT IN:

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

   :

   (1,6,4,5,7). , NOT IN (1,6,4,5,7).

   , .

: , , . , , id data value.

Answer 2

MYSQL

1: MySQL 8.0 WITH

2: ( TableName FROM

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