How to Remove Duplicate Emails While Keeping Other Column Duplicates in SQL?

Eliminating Email Duplicates in SQL Query with DISTINCT

In the provided SQL query, you aim to modify it to return unique Email entries while allowing duplicates in other columns. Since DISTINCT and GROUP BY operate on entire rows, a different approach is required to achieve this goal.

The solution lies in incorporating a subquery that leverages SQL Server's ROW_NUMBER() function. By partitioning the dataset based on Email and ordering it by descending ID using the ROW_NUMBER() function, we can assign a unique rank to each row within each Email group.

The main query then selects all the rows where the rank value (rn) is equal to 1, ensuring that only one row for each distinct Email is included in the results.

Here is the modified query:

SELECT *
FROM (
    SELECT ID, Email, ProductName, ProductModel,
        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
    FROM Products
) a
WHERE rn = 1

This query effectively eliminates duplicate Emails while preserving duplicates in other columns. You can further refine the query by adding conditional filters to the subquery, as demonstrated in this example:

SELECT *
FROM (
    SELECT ID, Email, ProductName, ProductModel,
        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn
    FROM Products
    WHERE ProductModel = 2 AND ProductName LIKE 'CYBER%'
) a
WHERE rn = 1

This modified query limits the results to products with a ProductModel of 2 and names that start with 'CYBER%', retaining the non-duplicate Email entries within these criteria.

The above is the detailed content of How to Remove Duplicate Emails While Keeping Other Column Duplicates in SQL?. For more information, please follow other related articles on the PHP Chinese website!