How to use RANK () in SQL Server

Question

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

Here is 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 

Results for this query:

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

What is my desired result:

 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 is the same value as 181 , then two numbers will have the same xRank .

Answer 1

Just change

 OVER (PARTITION BY ContenderNum ORDER BY totals ASC) 

to

 OVER (ORDER BY totals ASC) 

Take a look at this example.

SQL Fiddle DEMO

You can also see the difference between RANK (Transact-SQL) and DENSE_RANK (Transact-SQL)

RANK (Transact-SQL)

If two or more rows are snapped to a rank, each linked row gets the same rank. For example, if two top managers have the same SalesYTD value, they both took the same place. The seller with the next highest SalesYTD is in third place because there are two lines that are ranked higher. Therefore, the RANK function does not always return consecutive integers.

DENSE_RANK (Transact-SQL)

Returns the rank of the rows in the result set section without any spaces in the ranking. The rank of the line is a plus plus the number of excellent ranks that go before the line in question.

Answer 2

To answer the title of the question, “How to use Rank () in SQL Server”, here's how it works:

I will use this dataset 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 section that basically defines grouping.

In this example, if you split into 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", etc.

Ranks are solved as follows: The rank for each record is a plus plus the number of ranks that are in front of it in its section. The rank will only increase when one of the fields you selected (except for the fields (sections)) differs from those that were in front of it. If all the selected fields coincide, and the ranks are assigned and they are assigned a value, one.

Knowing this, if we only want 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

Answer 3

You need to use DENSE_RANK, not RANK. The only difference is that it leaves no spaces. You also should not split into contender_num, otherwise you put each opponent in a separate group, so each of them takes first place in their divided 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 

Hint for using StackOverflow, please write DDL and sample data so people can help you use less of your 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; 

Answer 4

DENSE_RANK () is a rank without spaces, i.e. he is "tight."

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

RANK () - contains a space between ranks.

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

Answer 5

You are already grouped by ContenderNum, no need to split it again. Use Dense_rank () and order by totals. 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 

Answer 6

 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