DISTINCT for only one Column

Question

Let's say I have the following query.

SELECT ID, Email, ProductName, ProductModel FROM Products

How can I modify it so that it returns no duplicate Emails?

In other words, when several rows contain the same email, I want the results to include only one of those rows (preferably the last one). Duplicates in other columns should be allowed.

Clauses like DISTINCT and GROUP BY appear to work on entire rows. So I'm not sure how to approach this.

Answer 1

For SQL Server 2005 or above, you can use the below code:

SELECT *

  FROM (SELECT  ID, Email, ProductName, 

                        ProductModel,

                        ROW_NUMBER() OVER(PARTITION BY Email ORDER BY ID DESC) rn

                    FROM Products

              ) a

WHERE rn = 1

EDIT: Here, is an example using a where clause:

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