Sql Server Ranking Value Not Changing Please Help
No need for the complex - and expensive - FIND_IN_SET() and GROUP_CONCAT() solution. All you need is the DENSE_RANK() function:

SELECT 
    id, name, score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS rank
FROM scores ;




If you want to add a WHERE clause, say WHERE name = 'C' and still get the rank(s) - among all rows of the table - you can use either a CTE or a derived table:

-- derived table
SELECT d.*
FROM
  (
    SELECT 
        id, name, score, 
        DENSE_RANK() OVER (ORDER BY score DESC) AS rank
    FROM scores
  ) AS d
WHERE d.name = 'C' ; -- arbitrary `WHERE`

-- CTE
WITH d AS
  (
    SELECT 
        id, name, score, 
        DENSE_RANK() OVER (ORDER BY score DESC) AS rank
    FROM scores
  )
SELECT d.*
FROM d
WHERE d.name = 'C' ; -- arbitrary `WHERE`