How to Remove All Duplicate Rows Except One Row?
When working with a database, it is common to encounter duplicate rows. These duplicates can arise from various sources such as data entry errors, incomplete normalization, or multiple inserts.
Removing all duplicate rows except one can be challenging but is essential for database maintenance and ensuring data accuracy. Below methods used to Remove All Duplicate Rows Except One record are as follows:
- Using GROUP BY and MIN/MAX Functions
- Using ROW_NUMBER() Window Function
- Using a Temporary Table
Let’s set up an environment to remove all duplicate rows
To understand How to Remove All Duplicate Rows Except One in MariaDB, we need a table structure on which we will perform various operations and queries. Here we will consider a table called employees which contains emp_id, emp_name, and emp_email as Columns.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(50),
emp_email VARCHAR(100)
);
-- Adding sample records to the employees table
INSERT INTO employees (emp_id, emp_name, emp_email) VALUES
(1, 'John Doe', 'john@example.com'),
(2, 'Jane Smith', 'jane@example.com'),
(3, 'John Doe', 'john@example.com'),
(4, 'Alice Johnson', 'alice@example.com'),
(5, 'Bob Williams', 'bob@example.com'),
(6, 'Jane Smith', 'jane@example.com');
Output:
Explanation: Our table has been created.
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.