How to use GROUP BY and MIN/MAX Functions In Databases
One common approach is to use the GROUP BY clause along with aggregate functions such as MIN or MAX to select one representative row for each set of duplicates. This method assumes that there are no other relevant columns that differentiate between duplicate rows.
Example:
-- Removing duplicate rows using GROUP BY and MIN/MAX functions
DELETE e1 FROM employees e1
JOIN employees e2 ON e1.emp_id > e2.emp_id
AND e1.emp_name = e2.emp_name
AND e1.emp_email = e2.emp_email;
-- Show the contents of the employees table after removing duplicates
SELECT * FROM employees;
Output:
Explanation: In the above query, We removes duplicate rows from the employees table by comparing each row e1 with every other row e2 based on the emp_id, emp_name, and emp_email columns. If a row e1 has a higher emp_id than another row e2 but the same emp_name and emp_email, it means that e1 is a duplicate and e2 is the original row to be kept.
The query uses a self-join to identify and delete duplicate rows. After executing the DELETE statement, the SELECT statement is used to display the contents of the employees table to show that duplicates have been removed.
How to Remove All Duplicate Rows Except One in MariaDB?
Duplicate rows in database tables can lead to complexity and data integrity issues and affect performance. Removing all duplicate rows while keeping one instance of each unique row is important for maintaining a clean database. In this article, we will explore various methods with the help of examples to keep our database clean and optimized in MariaDB.