If your sample data were entered differently, say in the order the competing produce were entered for judging, you could award an "early bird" prize to help encourage earlier entrants (and thereby get your weighing done ahead of time). Under this scenario, COLID would reflect the order the entries were received. The "early bird" prize could be given to the weightiest entrant among the first three entered in that category.

SELECT TOP 9
Entrant,
Weight,
Category,
DENSE_RANK() OVER (
PARTITION BY Category
ORDER BY Weight DESC, COLID
) AS [Rank]
FROM #ContestResults
--WHERE Category = 'Watermelon'
ORDER BY DENSE_RANK() OVER (
PARTITION BY Category
ORDER BY COLID
)
go

--SF