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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] How to use RANK() in SQL Server

Examples related to sql-server

Examples related to tsql