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