With
SQL Server 2005, Microsoft introduced a number of new features. These new
features make it easier for a DBA or SQL Server developer to code against and
to maintain SQL Server databases. In this article I will discuss the new
ranking functions provided with SQL Server 2005. These new functions make it
much easier for you to code your T-SQL to associate a ranking to your result
set. As I discuss each of the new ranking functions I will provide one or more
examples to help illustrate how these new functions work.
Ranking
functions are functions that allow you to sequentially number your result set.
These functions can be used to provide you with a number of different
sequential numbering schemes. For example you can number each row in your
result set sequentially where the first row has a ranking number of 1, the
second row has a ranking of 2, third row has 3, and so on. You can also use
these ranking functions to sequentual number groups, so each group would have a
numbering scheme of 1,2, 3, and then the next group would start over with 1, 2,
3, etc..
In
order to provide examples of each ranking function I need to have some test
data that the ranking function will process against. For my test data I will
use a simple “Person” table. This table will consist of three columns
“FirstName”, “Age” and “Gender”. Below is the code to create and populate my
sample test data file.
SET NOCOUNT ON
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’)
The
first ranking function I will discuss is the ROW_NUMBER function. This
function returns a sequential number starting at 1 for each row or grouping
within your result set. The ROW_NUMBER function has the following calling
syntax:
ROW_NUMBER ( ) 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 ROW_NUMBER function applies sequential numbering.
<order_by_clause>” is a column or set of columns used to order the result set within the grouping (partition).
To demonstrate how to use the ROW_NUMBER function, my first example below will sequentially number all the rows in my Person table, and order them by Age.
SELECT ROW_NUMBER() OVER (ORDER BY Age) AS [Row Number by Age],
FirstName,
Age
FROM Person
Here
is the result set for the above T-SQL code:
Row Number by Age FirstName Age
——————– ———- ———–
1 Larry 5
2 Doris 6
3 George 6
4 Mary 11
5 Sherry 11
6 Sam 17
7 Ted 23
8 Marty 23
9 Sue 29
10 Frank 38
11 John 40
Here
you can see I have sequentially numbered all my Person table rows starting from
1, and the result set is ordered by the Age column. This ordering was
accomplished by placing the “ORDER BY Age” criteria in the ORDER BY clause of
the ROW_NUMBER function.
Suppose
you do not want your result set to be ordered, but you want to simply just
sequentially number each row. The ROW_NUMBER function requires an ORDER BY clause,
so something is needed in this clause. In the following query I specified
“SELECT 1” for the ORDER BY clauses, doing this just returned my rows
physically how they where stored, and sequentially numbered them starting from
1:
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],
FirstName,
Age
FROM Person
Here
is the result set from running the above query:
Row Number by Record Set FirstName Age
———————— ———- ———–
1 Ted 23
2 John 40
3 George 6
4 Mary 11
5 Sam 17
6 Doris 6
7 Frank 38
8 Larry 5
9 Sue 29
10 Sherry 11
11 Marty 23
The
ROW_NUMBER function not only allows you to order the entire row set, but you
can also use the PARTITION clause to sequentially number groups of rows. Rows
will be sequentially numbered within each unique partition value. The
sequential number will restart at 1 for each new partition value in your record
set. Take a look at the following query:
SELECT ROW_NUMBER() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person
When
I run this query, this is my result set:
Partition by Gender FirstName Age Gender
——————– ———- ———– ——
1 Doris 6 F
2 Mary 11 F
3 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
5 Marty 23 M
6 Frank 38 M
7 John 40 M
In
this example I partitioned by Gender, and ordered by Age. Doing this allowed
me to sequentially number the female records in my Person table by age, and
then have the sequential numbering start over again for the male group.
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 be 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:
SELECT RANK() OVER (ORDER BY Age) AS [Rank by Age],
FirstName,
Age
FROM Person
Here
is the output for this command:
Rank by Age FirstName Age
——————– ———- ———–
1 Larry 5
2 Doris 6
2 George 6
4 Mary 11
4 Sherry 11
6 Sam 17
7 Ted 23
7 Marty 23
9 Sue 29
10 Frank 38
11 John 40
By
looking at this output you can see that whenever 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.
Below is an example where I grouped my ranking by Gender, and ordered each
ranking by Age:
SELECT RANK() OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
FirstName,
Age,
Gender
FROM Person
Here
is the results of running the above query:
Partition by Gender FirstName Age Gender
——————– ———- ———– ——
1 Doris 6 F
2 Mary 11 F
2 Sherry 11 F
4 Sue 29 F
1 Larry 5 M
2 George 6 M
3 Sam 17 M
4 Ted 23 M
4 Marty 23 M
6 Frank 38 M
7 John 40 M
Here
you can see that the “F” Gender started ranking at 1 and goes through 4, then
the ranking starts over with 1 when the first “M” Gender is encountered.
The
DENSE_RANK function is similar to the RANK function, although this function
doesn’t produce gaps in the ranking numbers. Instead this function sequentially
ranks each unique ORDER BY value. With the DENSE_RANK function each row
either has the same ranking as the preceeding row, or has a ranking 1 greater
then the prior row. The DENSE_RANK function has the same syntax as the RANK
function.
Here
I use the DENSE_RANK function to rank all my Person records by Age:
SELECT DENSE_RANK() OVER (ORDER BY Age) AS [Dense Rank by Age],
FirstName,
Age
FROM Person
This
code produces the following output:
Dense Rank by Age FirstName Age
——————– ———- ———–
1 Larry 5
2 Doris 6
2 George 6
3 Mary 11
3 Sherry 11
4 Sam 17
5 Ted 23
5 Marty 23
6 Sue 29
7 Frank 38
8 John 40
Here
you can see the “Dense Rank By Age” column numbers are sequential without any gaps.
And when two rows have the same ORDER BY value they have the same ranking like
“Ted” and “Marty”.
The
last ranking function is the NTILE function. This function is used to break
up a record set into a specific number of groups. The NTILE function also uses
similar syntax as the other ranking functions.
In
this first example, I want to group my Person records into three different
groups of records. I want these groups to be based on the Age column. To do
that I would run the following T-SQL:
SELECT FirstName,
Age,
NTILE(3) OVER (ORDER BY Age) AS [Age Groups]
FROM Person
Here
is my result set from the above T-SQL command:
FirstName Age Age Groups
———- ———– ——————–
Larry 5 1
Doris 6 1
George 6 1
Mary 11 1
Sherry 11 2
Sam 17 2
Ted 23 2
Marty 23 2
Sue 29 3
Frank 38 3
John 40 3
In
my result set I ended up with three different “Age Groups”. The first age
group goes from Age 5 to Age 11, the second age group goes from 11 to 23, and
the last age group is 29 to 40. The NTILE function just evenly divides your
record set into the number of groups the NTILE function requests. By using the
NTILE function each record in a group is give the same ranking.
The
NTILE function is very useful if you only want to return a specific grouping of
records. Below is an example where I returned only the middle group (Age Group
= 2) from my prior example:
SELECT FirstName,
Age,
Age AS [Age Group]
FROM ( SELECT FirstName,
Age,
NTILE(3) OVER (ORDER BY Age) AS AgeGroup
FROM Person) A
WHERE AgeGroup = 2
Here
you can see I only returned the second Age Group:
FirstName Age Age Group
———- ———– ———–
Sherry 11 11
Sam 17 17
Ted 23 23
Marty 23 23
Coding
a process to sequential number your record sets used to take a number of lines
of code. SQL Server 2005 solved this coding dilemma by providing some new
ranking functions. Hopefully the next time you need to sequential number a
record set, one of the new ranking functions in SQL Server 2005 will make your
number task, a trivial task.
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases--foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Property of TechnologyAdvice. © 2026 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.