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 something like this:

;WITH cte AS

(

SELECT *,

ROW_NUMBER() OVER (PARTITION BY Product ORDER BY SKU) AS rn

FROM TestData

)

SELECT *

FROM cte

WHERE rn = 1;