New Ranking Functions within SQL Server 2005
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 40Here 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 23The 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 MIn 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 40By 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 MHere 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 40Here 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 3In 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.Let's take following sample table and data to know about RANK,RANK_DENSE,NTILE,ROW_NUMBER with examples
Create table ExamResult(name varchar(50),Subject varchar(20),Marks int)
insert into ExamResult values('Adam','Maths',70)
insert into ExamResult values ('Adam','Science',80)
insert into ExamResult values ('Adam','Social',60)
insert into ExamResult values('Rak','Maths',60)
insert into ExamResult values ('Rak','Science',50)
insert into ExamResult values ('Rak','Social',70)
insert into ExamResult values('Sam','Maths',90)
insert into ExamResult values ('Sam','Science',90)
insert into ExamResult values ('Sam','Social',80)
RANK(): Returns the rank of each row in the result set of partitioned columnselect Name,Subject,Marks, RANK() over(partition by name order by Marks desc)Rank From ExamResult order by name,subjectDENSE_RANK() This is same as RANK() function. Only differencec is returns rank with out gaps.
select Name,Subject,Marks, DENSE_RANK() over(partition by name order by Marks desc)Rank From ExamResult order by namein RANK() result set screeshot, you can notice that there is gap in Rank(2) for the name Sam and same gap is removed in DENSE_RANK().
NTILE(): Distributes the rows in an ordered partition into a specified number of groups.
It devides the partitioned result set into specified number of groups in an order.
Example for NTILE(2):
select Name,Subject,Marks, NTILE(2) over(partition by name order by Marks desc)Quartile From ExamResult order by name,subjectExample for NTILE(3):
select Name,Subject,Marks, NTILE(3) over(partition by name order by Marks desc)Quartile From ExamResult order by name,subjectROW_NUMBER(): Returns the serial number of the row order by specified column.
select Name,Subject,Marks, ROW_NUMBER() over(order by Name) RowNumber From ExamResult order by name,subject
No comments:
Post a Comment