DISTINCT for only one column

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.

When you use `DISTINCT` think of it as a distinct row, not column. It will return only rows where the columns do not match exactly the same.

SELECT DISTINCT ID, Email, ProductName, ProductModel
FROM Products

----------------------
1 | [email protected] | ProductName1 | ProductModel1
2 | [email protected] | ProductName1 | ProductModel1

The query would return both rows because the `ID` column is different. I'm assuming that the `ID` column is an `IDENTITY` column that is incrementing, if you want to return the last then I recommend something like this:

SELECT DISTINCT TOP 1 ID, Email, ProductName, ProductModel
FROM Products
ORDER BY ID DESC

The `TOP 1` will return only the first record, by ordering it by the `ID` descending it will return the results with the last row first. This will give you the last record.

If you are using SQL Server 2005 or above use this:

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:
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

For Access, you can use the SQL Select query I present here:

For example you have this table:

CLIENTE|| NOMBRES || MAIL
----------------------------
888 || T800 ARNOLD || [email protected]

123 || JOHN CONNOR || [email protected]

125 || SARAH CONNOR ||[email protected]

And you need to select only distinct mails.
You can do it with this:

SQL SELECT:

SELECT MAX(p.CLIENTE) AS ID_CLIENTE
, (SELECT TOP 1 x.NOMBRES
FROM Rep_Pre_Ene_MUESTRA AS x
WHERE x.MAIL=p.MAIL
AND x.CLIENTE=(SELECT MAX(l.CLIENTE) FROM Rep_Pre_Ene_MUESTRA AS l WHERE x.MAIL=l.MAIL)) AS NOMBRE,
p.MAIL
FROM Rep_Pre_Ene_MUESTRA AS p
GROUP BY p.MAIL;

You can use this to select the maximum ID, the correspondent name to that maximum ID , you can add any other attribute that way. Then at the end you put the distinct column to filter and you only group it with that last distinct column.

This will bring you the maximum ID with the correspondent data, you can use min or any other functions and you replicate that function to the sub-queries.

This select will return:

CLIENTE|| NOMBRES || MAIL
----------------------------
888 || T800 ARNOLD || [email protected]

125 || SARAH CONNOR ||[email protected]


Remember to index the columns you select and the distinct column must have not numeric data all in upper case or in lower case, or else it won't work.
This will work with only one registered mail as well.
Happy coding!!!

The reason `DISTINCT` and `GROUP BY` work on entire rows is that your query returns entire rows.

To help you understand: Try to write out by hand what the query should return and you will see that it is ambiguous what to put in the non-duplicated columns.

If you literally don't care what is in the other columns, don't return them. Returning a random row for each e-mail address seems a little useless to me.

Try This

;With Tab AS (SELECT DISTINCT Email FROM Products)
SELECT Email,ROW_NUMBER() OVER(ORDER BY Email ASC) AS Id FROM Tab
ORDER BY Email ASC

This assumes SQL Server 2005+ and your definition of "last" is the max PK for a given email

WITH CTE AS
(
SELECT ID,
Email,
ProductName,
ProductModel,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY ID DESC) AS RowNumber
FROM Products
)
SELECT ID,
Email,
ProductName,
ProductModel
FROM CTE
WHERE RowNumber = 1

You can over that by using `GROUP BY` like this:

SELECT ID, Email, ProductName, ProductModel
FROM Products
GROUP BY Email

Try this:
```
SELECT ID, Email, ProductName, ProductModel
FROM Products
WHERE ID IN (SELECT MAX(ID) FROM Products GROUP BY Email)
```