SELECT
    ColID,
    Category,
    Weight
FROM dbo.ContestResults
ORDER BY DENSE_RANK() OVER (
    ORDER BY Weight DESC
)

SELECT
    ColID,
    Category,
    Weight
FROM dbo.ContestResults
ORDER BY Weight DESC