Ranking function helps in ranking each row after partitioning has been done.Numbering depends upon the function which is used and some rows may receive the same value as other.Rank functions are non deterministic in nature. It proves to be very useful when we need our result set to be numbered sequentially.
Firstly, the result sets produced by FROM clause are partitioned based on the column name mentioned in the PARTITION BY expression . PARTITION BY expression is optional and if the same is absent , the function assumes all rows of the result set as a single group. Once the partition has been done , ODER BY clause orders the rows within the partition and then any ranking is applied.
There are four RANKING functions which are discussed in detail in coming paragraph. We will be demonstrating the RANKING function with and without the use of PARTITION BY expression.Consider the below 'MarksInfo' table for illustration.
 |
| MarksInfo Table |
ROW_NUMBER(): It simply Returns the sequential number of the row order by specified column.
Syntax:ROW_NUMBER() OVER([PARTTION BY <ColumnName>] ORDER BY <ColumnName>)
A. With use of PARTITION BY clause
B. Without use of PARTITION BY clause
RANK(): It simply ranks each row of the partitioned result set. It ranks the same value to the rows that have the same ORDER BY value.The rank of rows is equal to the number of ranks plus one that come before to the row in concerned with in a partitioned group.Rank function does not always gives consecutive integers.
Syntax:RANK() OVER([PARTTION BY <ColumnName>] ORDER BY <ColumnName>)
A. With use of PARTITION BY clause
B. Without use of PARTITION BY clause
DENSE_RANK:It simply ranks each row of the partitioned result set. It ranks the same value to the rows that have the same ORDER BY value.The rank of row is equal to the count of distinct rows plus one that come before to the row in concerned with in a partitioned group.Rank function always gives consecutive integers.
Syntax:DENSE_RANK() OVER([PARTTION BY <ColumnName>] ORDER BY <ColumnName>)
A. With use of PARTITION BY clause
B. Without use of PARTITION BY clause
NTILE(): NTILE divides the rows in an ordered partitioned result set into a specified number of groups. The numbering of groups starts at one. For each row, NTILE returns the number of the group to which the row belongs.
If the number of rows in a partition is not divisible by integer_expression, this will lead to the formation of groups of two sizes that differ by one member. Larger groups will come before smaller groups in the order specified by the OVER clause.
Syntax:NTILE(integer_expression) OVER([PARTTION BY <ColumnName>] ORDER BY <ColumnName>)
A. With use of PARTITION BY clause
B. Without use of PARTITION BY clause
