New Ranking functions in SQL Server 2005

Along with SQL Server 2005, Microsoft introduced a number of new features and these features will make it easier for DBA or SQL Server specialists to code and maintain SQL Server databases. This article will discuss the new ranking functions provided in SQL Server 2005. These new features will make it easy to write T-SQL code to combine your aggregated result set. The lesson will guide each part of the new ranking functions and provide some examples to illustrate the function of the function.

What are Ranking functions?

Ranking functions allow you to number sequentially (rank) for result sets. These functions can be used to provide sequence numbers in different sequential numbering systems. You can easily understand the following: you have each number on each line in a row, in the first line ranked number 1, the second row ranks number 2 . You can use the ranking function in groups of weeks Self, each group will be numbered according to schemes 1,2,3 and the next group starts with 1,2,3 .

Test run data for examples

For some examples of ranking functions, I need to set up some test data. In the test data, I use a fairly simple 'Person' table. The table will include 3 columns 'FirstName', 'Age' and 'Gender'. The code below aims to create and record test data to the file.

SET NOCOUNT ON
CREATE TABLE Person (
FirstName VARCHAR (10),
INT Age,
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 function I want to talk about is ROW_NUMBER. This function returns a sequence of sequences starting at 1 for each row or group in the result set. The ROW_NUMBER function will have the following syntax:

ROW_NUMBER () OVER ([])

In it :

is the column or set of columns used to determine the grouping for the ROW_NUMBER function applied to sequential numbering.

is a column or set of columns used to sort the result set in a group (partition)

To learn more about how to use the ROW_NUMBER function, the example below will sequentially number all lines in the Person table and sort them by Age field.

SELECT ROW_NUMBER () OVER (ORDER BY Age) AS [Row Number by Age],
       FirstName,
       Age
  FROM Person

And this is the result set of T-SQL code on:

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

You can see that I have sequentially numbered all of the Person table rows starting at 1, and the result set is sorted by Age column. This arrangement is completed by the 'ORDER BY Age' standard in the ORDER BY clause of ROW_NUMBER function.

Suppose you do not want to collect your sorted results but want to return the table to sort by the number of records of each line. The ROW_NUMBER function always requires an ORDER BY clause, so you need to include a value in this clause. In the query function below I have specified 'SELECT 1' in the ORDER BY clause, this will only return the result as the table originally stored and of course the sequential numbering still starts from 1 :

SELECT ROW_NUMBER () OVER (ORDER BY (SELECT 1)) AS [Row Number by Record Set],
       FirstName,
       Age
  FROM Person

This is the result set when running the above query function:

Row Number by Record 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 sort the entire line set, but you can also use the PARTITION clause to filter out the number of lines to be numbered. Lines will be numbered sequentially in each unique PARTITION value. The numbered sequences will always start at 1 for each new PARTITION value in your record set. See the query function below

SELECT ROW_NUMBER () OVER (PARTITION BY Gender ORDER BY Age) AS [Partition by Gender],
       FirstName,
       Age,
       Gender
  FROM Person

When running the above query, the result set will output as follows:

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
first                     Doris       6            F
2                     Mary         11           F
3                     Sherry      11           F
4                     Sue         29           F
first                     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 sorted by Age. Practicing this example will allow me to sequentially record Female records in the Person table according to age, and then the numbering will start again with Male.

RANK function

Sometimes you want a line that has the same column sort value as the other lines have the same rank. If so, the RANK () function can help you. The function RANK has the following syntax:

RANK () OVER ([])

In it :

is a column or set of columns used to determine the continuous numbering in the RANK function

is a column or set of columns used to sort the result set in a group (partition)

The function RANK will continuously number a set of records but when there are 2 lines with the same value, the function will evaluate as the same value. The ranking value will still increase even when there are 2 lines of the same value, so when evaluating a value that is next sorted, the sequence number will continue to be hit but will increase 1 value to the next line. in the set.

This is an example of a rank function in a set of records sorted by Age:

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

And the results returned:

Rank by Age          FirstName  Age
-------------------- ---------- ------
first                     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
ten                    Frank       38
11                   John       40

As you can see, for lines with the same Age value, Rank by Age also has the same value. You can see Doris and George, Mary and Sherry, similar to Ted and Marty, each pair has the same Rank by Age value. Note that Doris and George have the same rating as 2, but Mary's rating (with the next Age value) is not 3 but 4. The reason is that Mary is returned to the 4th record in the set write, and the RANK () function takes that data when setting the next rank value in Rank by Age

If you want a lot of ratings in your record set, for each rating you need to set a specific group using the PARTITION BY clause in the RANK function. The example below shows the effect when I group the rankings according to Gender and sort by Age

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

This is the result of running the above query functions:

Partition by Gender  FirstName  Age         Gender
-------------------- ---------- ----------- ------
first                     Doris       6            F
2                     Mary        11           F
2                     Sherry      11           F
4                     Sue         29           F
first                     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

You can see that Gioitinh is 'F' which starts to rank from 1 to 4, then begins to renumber 1 from Gioitinh 'M'

DENSE_RANK function

The DENSE_RANK function is the same as the RANK function, however, this function does not provide a distance between the ratings numbers. Instead, this function will rank continuously for each specific ORDER BY value. With DENSE_RANK function, even if there are two lines with the same ranking value, the next line only adds one value to the upper line. The DENSE_RANK function has the same syntax as RANK function.

This is the DENSE_RANK function I used to classify all records in the Person table according to the Age field

SELECT DENSE_RANK () OVER (ORDER BY Age) AS [Dense Rank by Age],
       FirstName,
       Age
  FROM Person

The above code will output as follows:

Dense Rank by Age    FirstName  Age
-------------------- ---------- -----------
first                     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

 

 

As you can see the numbers in the 'Dense Rank By Age' column are still continuous, uninterrupted even when there are two lines with the ORDER BY and ranking values ​​like Ted and Marty.

NTILE function

The last function is the NTILE function. This is the function used to break the set of records in a specific number of groups. NTILE also uses syntax like other ranking functions.

In the first example of this function, I will group the Person table records into 3 different groups. I want these groups based on the Age column. To do this, I will run the following T-SQL:

SELECT FirstName,
       Age,
       NTILE (3) OVER (ORDER BY Age) AS [Age Groups]
  FROM Person

This is my result set from the T-SQL statement above:

FirstName  Age         Age Groups
---------- ----------- --------------------
Larry       5            first
Doris       6            first
George      6            first
Mary        11           first
Sherry      11           2
Sam         17           2
Ted          23           2
Marty       23           2
Sue         29           3
Frank       38           3
John       40          3

 

 

In the result set is available above with 3 different Age groups. The first group starts from 5 to 11 years old, the second group starts from 11 to 23 and the last group is from 29 to 40. NTILE function only works to divide the number of records evenly and put it into each number group. Using the NTILE function for each record in a group will add the same ratings.

NTILE is a very useful function if you only want to return a specific group in the records. Here is an example when I want to return only groups of people of average age (Group Age 2) from the above 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

Result of the above statement:

FirstName  Age         Age Group
---------- ----------- -----------
Sherry      11           11
Sam         17           17
Ted         23           23
Marty      23          23

 

 

Conclude

Encrypt a process that sorts sequential numbers in a set of records used to get some of the code lines. SQL Server 2005 offers a few new ranking functions. Hopefully in the future you will need to classify your record sets and one of the functions introduced in this article will help you complete that task, it is a completely simple task.