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

Question

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 colors ... I don't know, is there a better/more optimized way to do this?

I am using SQL Server 2008 express.

Answer 1

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

That should do the trick.

Answer 2

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;