I figured it out. I came up with the following:

RANKING AS

(

SELECT

ROW_NUMBER() OVER(PARTITION BY d.Patient ORDER BY d.visits DESC) AS row_num,

d.Provider,

d.Patient,

d.visits

FROM DETAILS d

)

SELECT

*

FROM RANKING r

WHERE r.row_num = 1

ORDER BY r.Provider;