TSQL - SELECT DISTINCT on one column

Question

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]

Answer 1

try this:

SELECT 
    t.*
    FROM TestData t
        INNER JOIN (SELECT
                        MIN(ID) as MinID
                        FROM TestData
                        WHERE SKU LIKE 'FOO-%'
                   ) dt ON t.ID=dt.MinID

EDIT
once the OP corrected his samle output (previously had only ONE result row, now has all shown), this is the correct query:

declare @TestData table (ID int, sku char(6), product varchar(15))
insert into @TestData values (1 ,  'FOO-23'      ,'Orange')
insert into @TestData values (2 ,  'BAR-23'      ,'Orange')
insert into @TestData values (3 ,  'FOO-24'      ,'Apple')
insert into @TestData values (4 ,  'FOO-25'      ,'Orange')

--basically the same as @Aaron Alton's answer:
SELECT
    dt.ID, dt.SKU, dt.Product
    FROM (SELECT
              ID, SKU, Product, ROW_NUMBER() OVER (PARTITION BY PRODUCT ORDER BY ID) AS RowID
              FROM @TestData
              WHERE  SKU LIKE 'FOO-%'
         ) AS dt
    WHERE dt.RowNumber=1
    ORDER BY dt.ID

Answer 2

You cannot do this without using an aggregate function. See my answer here.

You can do perhaps:

SELECT DISTINCT MIN([ID]), MIN([SKU]), [PRODUCT]
FROM ...
GROUP BY [ID], [SKU]

But you need to determine which aggregate function fits with your business needs.

Answer 3

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

Answer 4

SELECT min (id) AS 'ID', min(sku) AS 'SKU', Product
    FROM TestData
    WHERE sku LIKE 'FOO%' -- If you want only the sku that matchs with FOO%
    GROUP BY product 
    ORDER BY 'ID'