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

User · Answer

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

User · Answer

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

User · Answer

You can over that by using GROUP BY like this  SELECT ID  Email  ProductName  ProductModel FROM Products GROUP BY Email

User · Answer

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   something something com   ProductName1   ProductModel1 2   something something com   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

User · Answer

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

User · Answer

Try this   SELECT ID  Email  ProductName  ProductModel FROM Products WHERE ID IN  SELECT MAX ID  FROM Products GROUP BY Email

User · Answer

For Access  you can use the SQL Select query I present here   For example you have this table   CLIENTE   NOMBRES        MAIL  888       T800 ARNOLD    t800 arnold cyberdyne com  123       JOHN CONNOR    s connor skynet com  125       SARAH CONNOR   s connor skynet com  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    t800 arnold cyberdyne com  125       SARAH CONNOR   s connor skynet com  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

User · Answer

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

[sql] DISTINCT for only one column

Examples related to sql

Examples related to sql-server