How to Use the RANK Function in sql server

Introduction:

Sometimes you want a row that has the same order by column value as another row to have the same ranking.  If this is the case then the RANK() function will help you.  The RANK function has the following calling syntax:

RANK ( )  OVER ( [ <partition_by_clause> ] <order_by_clause> )

Where the:

 “<partition_by_clause>” is a column or set of columns used to determine the grouping in which the RANK function applies sequential numbering.

 "<order_by_clause>” is a column or set of columns used to order the result set within the grouping (partition).

The RANK function sequentially numbers a record set, but when two rows have the same order by value then they get the same ranking.  The ranking value still gets incremented when two rows have the same order by value, so that  when a new ranking order by value is encountered the ranking value on that new row will by 1 more than the number of proceeding rows.  Let me show you a couple of examples to help you better understand the RANK function.  

In this first example I want to rank my record set by Age.  If you look at the output of the first SELECT statement you can see that when ever rows have the same Age value their “Rank by Age” value are the same.  You can see this for “Doris” and “George”, “Mary” and “Sherry”, as well as “Ted” and “Marty”.  Each of these row pairs have the same “Rank by Age” value.  Note that “Doris” and “George” both have a ranking of 2, but the ranking for “Mary” the next unique Age doesn’t have a ranking value of 3, but instead has a ranking of 4.  This is because “Mary” is the forth record returned in the record set, and the RANK() functions takes this into account when setting the ranking value of the next unique “Rank by Age” value.

If you want to have multiple rankings in your record set, where each ranking is for a specific group you need to use the “PARTITION BY” clause of the RANK function.  The second SELECT statement will show how this work.  If you run this example you will see that the “F” Gender output from the second SELECT statement starts ranking at 1 and goes through 4, then the ranking starts over with 1 when the first “M” Gender is encountered.

Example:

SET NOCOUNT ON;

-- Sample Data
CREATE TABLE Person(
FirstName VARCHAR(10),
Age INT,
Gender CHAR(1));

INSERT INTO Person VALUES ('Ted',23,'M');
INSERT INTO Person VALUES ('John',40,'M');
INSERT INTO Person VALUES ('George',6,'M');
INSERT INTO Person VALUES ('Mary',11,'F');
INSERT INTO Person VALUES ('Sam',17,'M');
INSERT INTO Person VALUES ('Doris',6,'F');
INSERT INTO Person VALUES ('Frank',38,'M');
INSERT INTO Person VALUES ('Larry',5,'M');
INSERT INTO Person VALUES ('Sue',29,'F');
INSERT INTO Person VALUES ('Sherry',11,'F');
INSERT INTO Person VALUES ('Marty',23,'F');

-- Rank by Age
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
       FirstName,
       Age
  FROM Person;

-- Rank by Gender Order by Age
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
       FirstName,
       Age,
       Gender
  FROM Person;