DISTINCT for only one column

by

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

More Related Contents:

  1. How do I escape a single quote in SQL Server?
  2. NOT IN vs NOT EXISTS
  3. Simple way to transpose columns and rows in SQL?
  4. Only inserting a row if it’s not already there
  5. Unpivot with column name
  6. Selecting data from two different servers in SQL Server
  7. How can I delete using INNER JOIN with SQL Server?
  8. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  9. How to convert DateTime to VarChar
  10. Most efficient way in SQL Server to get date from date+time?
  11. Fastest way to perform nested bulk inserts with scope_identity() usage?
  12. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  13. SQL performance on LEFT OUTER JOIN vs NOT EXISTS
  14. Stored Procedure with optional “WHERE” parameters
  15. SQL Server: Howto get foreign key reference from information_schema?
  16. SCOPE_IDENTITY() for GUIDs?
  17. How do I perform a GROUP BY on an aliased column in SQL Server?
  18. How to execute .sql file using powershell?
  19. How to write UPDATE SQL with Table alias in SQL Server 2008?
  20. SQL Server 2008- Get table constraints
  21. Restrict an SQL Server connection to a specific IP address
  22. Can SQL Server Express LocalDB be connected to remotely?
  23. How to create a Date in SQL Server given the Day, Month and Year as Integers
  24. Count records for every month in a year
  25. Is there a severe performance hit for using Foreign Keys in SQL Server?
  26. How can I find Unicode/non-ASCII characters in an NTEXT field in a SQL Server 2005 table?
  27. Backup SQL Schema Only?
  28. Date serial in SQL?
  29. SQL Server: +(unary) operator on non-numeric Strings
  30. Parameterise table name in .NET/SQL?

Leave a Comment