Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 28, 2007

New Ranking Functions within SQL Server 2005

By Gregory A. Larsen

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.

What are Ranking Functions

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..

Test Data for My Examples

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')

ROW_NUMBER Function

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.

RANK Function

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.

DENSE_RANK Function

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”.

NTILE Function

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

Conclusion

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.

» See All Articles by Columnist Gregory A. Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM