first impression, distinct is lighter, rank is more expensive though it is new and cool. likes to see some document explain how each works. execution plan shows somewhat though.
I got a query from someone to run on my oracle server(realize the rank is used in SQL server 2012 too), it basically likes to export the number of users who logged in during a month against his database table auditevent(every login has a record), so count only once if a user logged in more than once.
he is using rank over and count the rank=1 so gives distinct value. his uses 2.697s, I replaced it with distinct clause which uses 2.60s, the time runs 0.097 second less. I thought should be much more less.
select count(userid) from (select USERID , RANK() OVER (PARTITION BY USERID ORDER BY LOGGEDTIME DESC,rownum) Rank_Line from CONCERTOAUDIT.AUDITEVENT where ...)
select count( distinct USERID) from ( select USERID from CONCERTOAUDIT.AUDITEVENT where..)