SELECT TOP 3
    Entrant,
    Weight,
    RANK() OVER (
        PARTITION BY Category
        ORDER BY Weight DESC
    ) AS [Rank]
FROM dbo.ContestResults
WHERE Category = 'Watermelon'
ORDER BY RANK() OVER (
    PARTITION BY Category
    ORDER BY Weight DESC
)

SELECT TOP 3
    Entrant,
    Weight,
    RANK() OVER (
        PARTITION BY Category
        ORDER BY Weight DESC
    ) AS [Rank]
FROM dbo.ContestResults
WHERE Category = 'Watermelon'