SELECT DISTINCT on one column

sql-server, t-sql

Solution

Assuming that you're on SQL Server 2005 or greater, you can use a CTE with ROW_NUMBER():

SELECT  *
FROM    (SELECT ID, SKU, Product,
                ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowNumber
         FROM   MyTable
         WHERE  SKU LIKE 'FOO%') AS a
WHERE   a.RowNumber = 1

Problem

Using SQL Server, I have... ``` ID SKU PRODUCT ======================= 1 FOO-23 Orange 2 BAR-23 Orange 3 FOO-24 Apple 4 FOO-25 Orange ``` I want ``` 1 FOO-23 Orange 3 FOO-24 Apple ``` This query isn't getting me there. How can I SELECT DISTINCT on just one column? ``` SELECT [ID],[SKU],[PRODUCT] FROM [TestData] WHERE ([PRODUCT] = (SELECT DISTINCT [PRODUCT] FROM [TestData] WHERE ([SKU] LIKE 'FOO-%')) ORDER BY [ID] ```

Original source