SELECT DISTINCT on one column

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 aWHERE   a.RowNumber = 1

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])

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.RowID=1    ORDER BY dt.ID