How to use RANK() in SQL Server

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) rnkfrom 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 | 1AAA | SKA | January, 31 2013 00:00:00+0000 |15 | 1AAA | 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 totalsFROM dbo.Cat1GroupImpersonationGROUP BY ContenderNum) AS aorder 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 select1,196,0,0,0 union all select2,181,0,0,0 union all select3,192,0,0,0 union all select4,181,0,0,0 union all select5,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 totalsFROM dbo.Cat1GroupImpersonationGROUP BY ContenderNum) AS a

SELECT contendernum,totals, RANK() OVER (ORDER BY totals ASC) AS xRank FROM(SELECT ContenderNum ,SUM(Criteria1+Criteria2+Criteria3+Criteria4) AS totalsFROM dbo.Cat1GroupImpersonationGROUP 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