sql server query - is writing multiple queries the only way?

If I have a table of fruits:

FruitId ColorId NumofPurchased.

I want to return a row for each of the colorId's where NumOfPurchased is max for that colorId. I am coming up with approaches like looping through the colorId's or manually write 10 queries for 10 col开发者_JAVA百科ors ... I don't know, is there a better/more optimized way to do this?

I am using SQL Server 2008 express.

SELECT MAX(NumOfPurchased), ColorId, FruitId
FROM fruit
GROUP BY ColorId, FruitId

That should do the trick.

Not sure if this is exactly what you want (whether it's the max for each colour across all fruit, or the max for each colour per fruit) - but you can change/add values to the PARTITION BY clause if necessary.

;with fruitCTE
AS
(
    SELECT  FruitId, ColorId, NumofPurchased,
            ROW_NUMBER() OVER (PARTITION BY ColorId,
                               ORDER BY NumofPurchased DESC
                              ) AS rn
    FROM    fruit
)
SELECT FruitId, ColorId, NumofPurchased,
FROM fruitCTE
WHERE rn = 1;