Ranking functionalities are used to give auto generated serial number to each record in a table or result set.
There are 4 types are ranking functions in SQL server.
ROW_NUMBER(),
RANK(),
DENSE_RANK(),
NTILE(n)
Row_Number()
Row number clause is
used to give auto generated serial number to the table.
Based on the order by
clause mentioned, auto serial number will be added to the table.
|
Value
|
Sno
|
|
40
|
1
|
|
45
|
2
|
|
60
|
3
|
|
75
|
4
|
Rank ():
Rank function is used to give rank to the values. Based on
the order by column given, Rank will be generated. Let us suppose I have a
table s shown below, Here I given rank order by the column Value.
|
Value
|
Rank
|
|
40
|
1
|
|
45
|
2
|
|
60
|
3
|
|
75
|
4
|
|
75
|
4
|
|
76
|
6
|
Here rank function give same number to the marching records. As shown in the above table, two records are having same number 75. For those two rank will be generated with 4 and next record gets the rank 6.(5 skips here).
Dense_Rank ():
Rank function is used to give rank to the values. Based on the order by column given, Rank will be generated. Let us suppose I have a table s shown below, Here I given rank order by the column Value.
Value
|
Rank
|
40
|
1
|
45
|
2
|
60
|
3
|
75
|
4
|
75
|
4
|
76
| 5 |
Here Dense_Rank function give same number to the marching records. As shown in the above table, two records are having same number 75. For those two rank will be generated with 4 and next record gets the rank 5 (This is the difference between Rank and Dense_Rank() functions).
Ntile(n):
Ntile splits the record set to number of groups (number defined by the user) and gives unique serial number to each group.
Ntile(3)
Value
|
Rank
|
40
|
1
|
45
|
1
|
60
| 2 |
75
| 2 |
75
| 3 |
76
| 3 |
Let us suppose, we have a table tbl_studentinfo
|
Sno
|
Name
|
Gender
|
Marksperncet
|
|
1
|
Supriya
|
F
|
69.49
|
|
2
|
Syamala
|
F
|
93.35
|
|
3
|
Sumithra
|
F
|
99.99
|
|
4
|
Mahesh
|
M
|
64.99
|
|
5
|
Sameer
|
M
|
74.99
|
|
6
|
Sandeep
|
M
|
74.99
|
|
7
|
Vijay
|
M
|
84.07
|
|
8
|
Sanjay
|
M
|
84.99
|
|
9
|
Sekhar
|
M
|
94.99
|
|
10
|
Sujan
|
M
|
99.49
|
If we run Ranking functionalities result will be as below.
SELECT *,ROW_NUMBER() OVER(ORDER BY Marksperncet) AS Rowno,
RANK() OVER(ORDER BY Marksperncet) AS Rankno,
DENSE_RANK() OVER(ORDER BY Marksperncet) AS DensiRno,
NTile(4) OVER(ORDER BY Marksperncet) AS Ntileno FROM tbl_StudentInfo
|
Sno
|
Name
|
Gender
|
Marksperncet
|
Rowno
|
Rankno
|
DensiRno
|
Ntileno
|
|
4
|
Mahesh
|
M
|
64.99
|
1
|
1
|
1
|
1
|
|
1
|
Supriya
|
F
|
69.49
|
2
|
2
|
2
|
1
|
|
5
|
Sameer
|
M
|
74.99
|
3
|
3
|
3
|
1
|
|
6
|
Sandeep
|
M
|
74.99
|
4
|
3
|
3
|
2
|
|
7
|
Vijay
|
M
|
84.07
|
5
|
5
|
4
|
2
|
|
8
|
Sanjay
|
M
|
84.99
|
6
|
6
|
5
|
2
|
|
2
|
Syamala
|
F
|
93.35
|
7
|
7
|
6
|
3
|
|
9
|
Sekhar
|
M
|
94.99
|
8
|
8
|
7
|
3
|
|
10
|
Sujan
|
M
|
99.49
|
9
|
9
|
8
|
4
|
|
3
|
Sumithra
|
F
|
99.99
|
10
|
10
|
9
|
4
|
Partition By
Partition by clause divides result set into partitions and gives separate ranking to each partition starting from 1.
SELECT *,ROW_NUMBER() OVER(Partition By Gender ORDER BY Marksperncet) AS Rowno,
RANK() OVER(Partition By Gender ORDER BY Marksperncet) AS Rankno,
DENSE_RANK() OVER(Partition By Gender ORDER BY Marksperncet) AS DensiRno,
NTile(2) OVER(Partition By Gender ORDER BY Marksperncet) AS Marksperncet FROM tbl_StudentInfo
|
Sno
|
Name
|
Gender
|
Marksperncet
|
Rowno
|
Rankno
|
DensiRno
|
Marksperncet
|
|
1
|
Supriya
|
F
|
69.49
|
1
|
1
|
1
|
1
|
|
2
|
Syamala
|
F
|
93.35
|
2
|
2
|
2
|
1
|
|
3
|
Sumithra
|
F
|
99.99
|
3
|
3
|
3
|
2
|
|
4
|
Mahesh
|
M
|
64.99
|
1
|
1
|
1
|
1
|
|
5
|
Sameer
|
M
|
74.99
|
2
|
2
|
2
|
1
|
|
6
|
Sandeep
|
M
|
74.99
|
3
|
2
|
2
|
1
|
|
7
|
Vijay
|
M
|
84.07
|
4
|
4
|
3
|
1
|
|
8
|
Sanjay
|
M
|
84.99
|
5
|
5
|
4
|
2
|
|
9
|
Sekhar
|
M
|
94.99
|
6
|
6
|
5
|
2
|
|
10
|
Sujan
|
M
|
99.49
|
7
|
7
|
6
|
2
|
In the above example, we applied partition to Gender column. So, the result set divided into two partitions (M and F) and each partition got separate Ranking function starting from 0.
No comments:
Post a Comment