SELECT          
        ROW_NUMBER() OVER (ORDER BY TotalScore DESC) AS RowNo,
        TotalScore, SupplierCode, ItemCode
            INTO        #result
            FROM        #temptable

WITH CTE AS (
    SELECT 
        *,
        RankValue = RANK() OVER (ORDER BY TotalScore DESC)
    FROM YourTable
)
UPDATE CTE
SET rank = RankValue;