SELECT DISTINCT on one column

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]

Solution 1:

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

Solution 2:

The simplest solution would be to use a subquery for finding the minimum ID matching your query. In the subquery you use GROUP BY instead of DISTINCT:

SELECT * FROM [TestData] WHERE [ID] IN (
   SELECT MIN([ID]) FROM [TestData]
   WHERE [SKU] LIKE 'FOO-%'
   GROUP BY [PRODUCT]
)

SELECT DISTINCT on one column

Solution 1:

Solution 2:

Related

Recent Posts