In this post will learn How to Delete all Duplicate Rows except for One in MySQL which we can perform using the combinations of different statements like group by, Min, and Max functions, Where group by the function will help in grouping the duplicate values or the rows which is completely based on the specified columns and whereas min and max function will help in selecting the row which is based on the criteria we have provided.
Delete All The Duplicate Rows
We have taken an example to show how we can remove the values of the duplicate row from the table except one so that it will become an original row So take an example of the given table below:
students +------+---------+-------+ | id | name | score | +------+---------+-------+ | 1 | John | 80 | | 2 | Mary | 90 | | 3 | John | 85 | | 4 | Alice | 95 | | 5 | John | 75 | +------+---------+-------+
And here we can see how john is being repeated multiple times which is simply nothing but duplicate values so for the same or removing the duplicates we have given a code below to implement and get it done:
DELETE s1 FROM students s1 JOIN students s2 ON s1. name = s2. name AND s1. score < s2. score GROUP BY s1. id;
In the query, given above we use a self-join on the “students” table to compare each row with all other rows with the same “name” value and select the row with the highest “score” value for each group of duplicate rows using the GROUP BY clause and the MAX function.
After that, we just delete the rows that are not the row with the highest “score” value using the DELETE statement and the alias s1 to refer to the table “students”. The self-join is performed using the alias s2.
Now the same table which was earlier will look different which is given below.
students +------+---------+-------+ | id | name | score | +------+---------+-------+ | 2 | Mary | 90 | | 3 | John | 80 | | 4 | Alice | 95 | +------+---------+-------+
where we have taken the first occurrence of the duplicate value as the original which is still there in the final table too and all other values which were repeating are removed so that we could only holds the original data for our final presentation.
To learn more about Delete all Duplicate Rows except for One in MySQL visit: by stack overflow in MySQL
To learn more about MySQL tutorials and the solutions of the problems along with concepts and tutorials of solutions list and learn visit: MySQL Tutorials And Problems.
