Which of the following SELECT statement would accomplish this task?

You work as a SQL Server 2012 database developer at ABC.com. You are developing a query for
a database driven Web application that allows visitors to vote for the cricket player of the week.
The number of votes is stored in a table named WeeklyVotes that has columns named Week,
PlayerName, Votes.
You need to write a Transact-SQL query that returns the cricket player that received the most
votes for each week, as well as the number of votes they received.
Which of the following SELECT statement would accomplish this task?

A.
SELECT PlayerName, Votes
FROM ( SELECT PlayerName, Votes,
RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank
FROM WeeklyVotes ) AS tmp
WHERE Rank = 1

B.
SELECT PlayerName, Votes
FROM ( SELECT PlayerName, Votes,
RANK() OVER (PARTITION BY Week ORDER BY Votes DESC) AS Rank FROM WeeklyVotes)
AS tmp
WHERE Rank = 1

C.
SELECT PlayerName, Votes
FROM ( SELECT TOP 1 PlayerName, Votes,
RANK () OVER (PARTITION BY PlayerName ORDER BY Votes ASC) AS Rank
FROM WeeklyVotes
ORDER BY Rank) AS tmp

D.
SELECT PlayerName, Votes
FROM ( SELECT TOP 1 PlayerName, Votes,
RANXO OVER (PARTITION BY PlayerName ORDER BY Votes DESC) AS Rank
FROM WeeklyVotes
ORDER BY Rank) AS tmp

Explanation: