MariaDB delete duplicate using ROW_NUMBER()

Please note : Window Functions are available in MariaDB 10.2 onwards

In our previous article, we talked about deleting duplicate data in MySQL using join. In this article we are going to demo on how to delete duplicate using row_number(). Since we already have our demo table created with dummy data, going ahead directly with the query

Step 1: Find duplicate using row_number()

SELECT  id  FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num FROM demo) d WHERE row_num > 1;
+------+
| id   |
+------+
|    7 |
|    3 |
+------+
2 rows in set (0.000 sec)

Step 2: Select * from demo

 select * from demo;

+------+--------+

| id   | name   |

+------+--------+

|    1 | abc    |

|    2 | abd    |

|    3 | vijay  |

|    4 | vijay  |

|    5 | ajay   |

|    6 | sanjay |

|    7 | vijay  |

+------+--------+

7 rows in set (0.000 sec)

Step 3: Delete duplicate

DELETE FROM demo WHERE id IN (SELECT id FROM (SELECT id,ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num FROM demo) d  WHERE row_num > 1);
Query OK, 2 rows affected (0.005 sec)

Step 4: Check if duplicate values are deleted using step 2 query

SELECT  id  FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num FROM demo) d WHERE row_num > 1;
Empty set (0.000 sec)

Done, duplicate data deleted.