text: Q:
  
  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;
  
  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`
  
  