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.

Solution 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.

Solution 2

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

Solution 3

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;

Solution 4

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]

View more solutions

Comments

[Ankursonikajen]

Try This : docs.microsoft.com/en-us/sql/t-sql/functions/rank-transact-s‌​ql You might also want to have a look at the difference between RANK (Transact-SQL) and DENSE_RANK (Transact-SQL)

[Prince Jea]

I want the highest total to be no. 1 and the second to the highest to be no.2 ...your answer didn't work but still thanks.

[Adriaan Stander]

Change the ASC to DESC as in the DEMO.

[Noel]

how is this answer different form other answers?

[Sliq]

@Ramblin'Man Because it's totally different.

[Rajenthiran T]

We can add three subject and set rank for that.

[Samuel Smith]

Your SQL Fiddle DEMO doesn't work any longer. Probably because fiddle is now supporting SQL Server 2017 and not 2008.

[Rajenthiran T]

we can't sum all the student mark or any finance related every row wise, in this query useful to that operation.

[Talha]

I want to rank 0 if there is 0 in totals.