How to use RANK() in SQL Server

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.

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.

To answer your question title, "How to use Rank() in SQL Server," this is how it works:

I will use this set of data as an example:

create table #tmp
(
  column1 varchar(3),
  column2 varchar(5),
  column3 datetime,
  column4 int
)

insert into #tmp values ('AAA', 'SKA', '2013-02-01 00:00:00', 10)
insert into #tmp values ('AAA', 'SKA', '2013-01-31 00:00:00', 15)
insert into #tmp values ('AAA', 'SKB', '2013-01-31 00:00:00', 20)
insert into #tmp values ('AAA', 'SKB', '2013-01-15 00:00:00', 5)
insert into #tmp values ('AAA', 'SKC', '2013-02-01 00:00:00', 25)

You have a partition which basically specifies grouping.

In this example, if you partition by column2, the rank function will create ranks for groups of column2 values. There will be different ranks for rows where column2 = 'SKA' than rows where column2 = 'SKB' and so on.

The ranks are decided like this: The rank for every record is one plus the number of ranks that come before it in its partition. The rank will only increment when one of the fields you selected (other than the partitioned field(s)) is different than the ones that come before it. If all of the selected fields are the same, then the ranks will tie and both will be assigned the value, one.

Knowing this, if we only wanted to select one value from each group in column two, we could use this query:

with cte as 
(
  select *, 
  rank() over (partition by column2 
             order by column3) rnk
  from t
              
) select * from cte where rnk = 1 order by column3;

Result:

COLUMN1 | COLUMN2	| COLUMN3	                        |COLUMN4 | RNK
------------------------------------------------------------------------------
AAA	    | SKB	| January, 15 2013 00:00:00+0000	|5	 | 1
AAA	    | SKA	| January, 31 2013 00:00:00+0000	|15	 | 1
AAA	    | SKC	| February, 01 2013 00:00:00+0000	|25	 | 1

SQL DEMO

You have to use DENSE_RANK rather than RANK. The only difference is that it doesn't leave gaps. You also shouldn't partition by contender_num, otherwise you're ranking each contender in a separate group, so each is 1st-ranked in their segregated groups!

SELECT contendernum,totals, DENSE_RANK() OVER (ORDER BY totals desc) AS xRank FROM
(
SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
FROM dbo.Cat1GroupImpersonation
 GROUP BY ContenderNum
) AS a
order by contendernum

A hint for using StackOverflow, please post DDL and sample data so people can help you using less of their own time!

create table Cat1GroupImpersonation (
contendernum int,
criteria1 int,
criteria2 int,
criteria3 int,
criteria4 int);

insert Cat1GroupImpersonation select
1,196,0,0,0 union all select
2,181,0,0,0 union all select
3,192,0,0,0 union all select
4,181,0,0,0 union all select
5,179,0,0,0;

DENSE_RANK() is a rank with no gaps, i.e. it is “dense”.

select Name,EmailId,salary,DENSE_RANK() over(order by salary asc) from [dbo].[Employees]

RANK()-It contain gap between the rank.

select Name,EmailId,salary,RANK() over(order by salary asc) from [dbo].[Employees]

You have already grouped by ContenderNum, no need to partition again by it. Use Dense_rank()and order by totals desc. In short,

SELECT contendernum,totals, **DENSE_RANK()** 
OVER (ORDER BY totals **DESC**) 
AS xRank 
FROM
(
   SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totals
   FROM dbo.Cat1GroupImpersonation
   GROUP BY ContenderNum
) AS a

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

RANK() is good, but it assigns the same rank for equal or similar values. And if you need unique rank, then ROW_NUMBER() solves this problem

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

Select T.Tamil, T.English, T.Maths, T.Total, Dense_Rank()Over(Order by T.Total Desc) as Std_Rank From (select Tamil,English,Maths,(Tamil+English+Maths) as Total From Student) as T

enter image description here