Wednesday, April 6, 2011

Ranking Functions in SQL Server

In Oracle we had rownum function which was used to assign a number for each row. Same way, SQL Server 2005 introduce four Ranking functions to assign a number to each row. Each function has unique features. Let’s discus in detail.

Ranking functions allow you sequentially number your result set. There are four Ranking function.

1) Row_Number() :- Returns the Sequential number for each row in increasing order start from 1.

Syntax : Row_Number() over(partition_clause|order_by_clause)

Suppose you have table named Employee
Table 1
Name                Age              Sex
Asim                20                M
Amir                20                M
Anas                24                M
Shabnam             17                F
Shradha             19                F
Rachna              21                F

Use RowNumber() with order by clause.

Select ROW_NUMBER() over(order by Age) as RowNumber,Name,Age,Sex from Employee

Table 2
RowNumber          Name          Age          Sex
1                 Shabnam        17            F
2                 Shradha        19            F
3                 Asim           20            M
4                 Amir           20            M
5                 Rachna         21            F
6                 Anas           24            M


If you don’t want to sort the table but want the data with RowNumber you can use Select 1 statement as Row_Number required order by clause

Select ROW_NUMBER() over(order by (Select 1)),Name,Age,Sex from Employee

Table 3
RowNumber          Name          Age         Sex
1                  Asim          20           M
2                  Amir          20           M
3                  Anas          24           M
4                  Shabnam       17           F
5                  Shradha       19           F
6                  Rachna        21           F


-- Use Row Number with partition by clause
Select ROW_NUMBER() over(partition by Sex order by age),Name,Age,Sex from Employee

Table 4
RowNumber          Name          Age          Sex
1                 Shabnam        17            F
2                 Shradha        19            F
3                 Rachna         21            F
1                 Asim           20            M
2                 Amir           20            M
3                 Anas           24            M

--You can not use Row_Number without order by clause
Select ROW_NUMBER() over(),Name,Age,Sex from Employee

Msg 4112, Level 15, State 1, Line 1
The ranking function "ROW_NUMBER" must have an ORDER BY clause.

May be you have noticed one thing in above tables Amir and Asim have same age but have different row number. If you want the same sequence number for rows that have same value than you have to use rank function for this. But it leave a gap between sequence number.
For an example we have same value for Amir and Asim 20,20. Sequence number for Asim and Amir is 3 but sequence number for Rachna is 5. (4 is missing here you can say draw back or advantage of rank function according to your requirement).

-- Use rank() with order by caluse
Select rank() over(order by Age) as RowNumber,Name,Age,Sex from Employee

Table 5
RowNumber          Name          Age          Sex
1                 Shabnam        17            F
2                 Shradha        19            F
3                 Asim           20            M
3                 Amir           20            M
5                 Rachna         21            F
6                 Anas           24            M


There is no use of Select 1 statement with rank function as it will give you 1 for all row in the table.

Select rank() over(order by (Select 1)),Name,Age,Sex from Employee

Table 6
RowNumber          Name          Age         Sex
1                  Asim          20           M
1                  Amir          20           M
1                  Anas          24           M
1                  Shabnam       17           F
1                  Shradha       19           F
1                  Rachna        21           F

partition clause also can use with rank function also output will be same as for RowNumber except one thing. All rows having same value will assign same number.

-- Use rank() with partition by clause
Select rank() over(partition by Sex order by age),Name,Age,Sex from Employee

Table 7
RowNumber          Name          Age          Sex
1                 Shabnam        17            F
2                 Shradha        19            F
3                 Rachna         21            F
1                 Asim           20            M
1                 Amir           20            M
3                 Anas           24            M

order by clause is must for rank function also.

If you will see the above tables(for rank), you will notice a gap between numbers. In table 4 Rachna has sequence number 5 which should be 4. for this you can use dense_rank(). It same as rank but it did not leave the gap.

-- Use dense_rank() with order by clause
Select dense_rank() over(order by Age) as RowNumber,Name,Age,Sex from Employee.

Table 8
RowNumber          Name          Age          Sex
1                 Shabnam        17            F
2                 Shradha        19            F
3                 Asim           20            M
3                 Amir           20            M
4                 Rachna         21            F
5                 Anas           24            M

all others would change as well.

Have you missed one thing so far, yes grouping with sequence number. SQL server provide a function name NTILE(TOTAL_GROUP_NUMBER). NTILE function divide the table in groups as many you have given in braces(TOTAL_GROUP_NUMBER). And than assign the same number for each member of the same group.

Let’s see the example for detail. Suppose you want to divide the given table in 3 groups than the query will be.

-- Use NTILE(3) with order by caluse
Select NTILE(3) over(order by Age) as RowNumber,Name,Age,Sex from Employee

Table 9
RowNumber          Name          Age          Sex
1                 Shabnam        17            F
1                 Shradha        19            F
2                 Asim           20            M
2                 Amir           20            M
3                 Rachna         21            F
4                 Anas           24            M



NTILE first divide the Total Rows by Total Group Number. And calculate how many rows should be come in one group.

Here group number is 3 and Total Rows are 6 So there will be 6/3 =2 rows in each group.

NTILE start making group from start and assign the same number for each row of the group. In above example Shabnam and Shardha are in the same group it has assign the same number that is 1 (starting number), Asim and an Amir are in the second group and have the same sequence number that 2. , Rachna and Anas are in the third group number that is 3.

It makes the group on the basis of FCS.

You will give Group number 6 than output will be different. In this case Total Rows in each groups is 6/6=1.

Table 10
RowNumber          Name          Age          Sex
1                 Shabnam        17            F
2                 Shradha        19            F
3                 Asim           20            M
4                 Amir           20            M
5                 Rachna         21            F
6                 Anas           24            M

Total group number may less ,equal or greater than total number of rows in table.


Other queries can be checked as well

-- If you don't want to sort the table but want the data with NTILE(3) you can use Select 1 statement as rank required order by clause
Select NTILE(3) over(order by (Select 1)),Name,Age,Sex from Employee

-- Use NTILE(3) with partition by clause
Select NTILE(2) over(partition by Sex order by age),Name,Age,Sex from Employee

You can download the complete script : Rankning Functions

No comments:

Followers

Link