How Can I Eliminate Duplicate Emails in a SQL SELECT Statement While Retaining Other Column Data?

Eliminating Duplicate Emails in SELECT with DISTINCT for Single Column

In a query that retrieves multiple columns from a table, it may be desirable to exclude duplicate rows based on a specific column while preserving distinct values in other columns. This can be challenging as traditional clauses like DISTINCT and GROUP BY operate on entire rows.

To address this need, the ROW_NUMBER() function can be employed. This function assigns a unique sequential number to each row within a partition defined by a specified column. By combining ROW_NUMBER() with the PARTITION BY and ORDER BY clauses, you can create a ranking of rows within each partition based on the values in the desired column.

Consider the following query:

SELECT ID, Email, ProductName, ProductModel
FROM Products

To modify this query so that it returns only unique emails, we can use the following code:

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

The ROW_NUMBER() function divides the results into partitions based on the Email column and assigns each row a unique rank within its partition. The subsequent ORDER BY clause ensures that the last-occurring row in each partition is ranked first. The final WHERE clause filters the results to only include rows with a rank of 1, effectively eliminating duplicate emails.

This approach allows you to specify any column as the basis for partitioning and ranking, providing flexibility in tailoring the query to your specific requirements.

The above is the detailed content of How Can I Eliminate Duplicate Emails in a SQL SELECT Statement While Retaining Other Column Data?. For more information, please follow other related articles on the PHP Chinese website!