Sql Server Ranking Value Not Changing Please Help How to convert this (MySQL Query) to SQL Server?

Or how to get a ranking value not changing while I'm using where in query:

SELECT id, 
       name, 
       score, 
       FIND_IN_SET( score, (SELECT GROUP_CONCAT( DISTINCT score
                            ORDER BY score DESC ) FROM scores)
                  ) AS rank
FROM scores WHERE name = A;
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`