How to use ROW_NUMBER() Window Function In Databases
MariaDB has introduced support for window functions including ROW_NUMBER() which assigns a unique sequential integer to each row within a partition of a result set. We can take advantage of this function to remove duplicate rows.
Example:
-- Removing duplicate rows using ROW_NUMBER() window function
DELETE FROM employees
WHERE (emp_name, emp_email) IN (
SELECT emp_name, emp_email
FROM (
SELECT emp_name, emp_email,
ROW_NUMBER() OVER (PARTITION BY emp_name, emp_email ORDER BY emp_id) AS rn
FROM employees
) AS sub
WHERE rn > 1
);
-- 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 using the ROW_NUMBER() window function. It assigns a unique sequential integer (rn) to each row within a partition of the result set defined by emp_name and emp_email, ordered by emp_id.
The innermost SELECT statement generates row numbers for each duplicate row (rn > 1) within each partition. The middle SELECT statement retrieves emp_name and emp_email along with their row numbers. The outer DELETE statement then deletes rows where emp_name and emp_email are duplicates (rn > 1).
Finally, the SELECT statement displays the contents of the employees table after removing duplicates.
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.