tsql - How to use RANK() in SQL Server

Question

I have a problem using RANK() in SQL Server.

Here’s my code:

SELECT contendernum,
       totals, 
       RANK() OVER (PARTITION BY ContenderNum ORDER BY totals ASC) AS xRank
FROM (
   SELECT ContenderNum,
          SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
   FROM Cat1GroupImpersonation
   GROUP BY ContenderNum
) AS a

The results for that query are:

contendernum    totals    xRank
          1       196        1
          2       181        1
          3       192        1
          4       181        1
          5       179        1

What my desired result is:

contendernum    totals    xRank
          1       196        1
          2       181        3
          3       192        2
          4       181        3
          5       179        4

I want to rank the result based on totals. If there are same value like 181, then two numbers will have the same xRank.

Answer 1

Change:

RANK() OVER (PARTITION BY ContenderNum ORDER BY totals ASC) AS xRank

to:

RANK() OVER (ORDER BY totals DESC) AS xRank

---

Have a look at this example:

SQL Fiddle DEMO

You might also want to have a look at the difference between RANK (Transact-SQL) and DENSE_RANK (Transact-SQL):

RANK (Transact-SQL)

If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one. The salesperson with the next highest SalesYTD is ranked number three, because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

DENSE_RANK (Transact-SQL)

Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.