Here we are discussing about who record have max ranking and display with distinct record.

SELECT * FROM (SELECT TableName.*,ROW_NUMBER() OVER (PARTITION BY Column ORDER BY Column DESC) AS RN FROM TableName) AS t where RN=1 ORDER BY Column 

Ex. SELECT * FROM (SELECT test.*,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Ranking DESC) AS RN FROM test) AS t where RN=1 ORDER BY ID