Difference between Row_Number, Rank, Dense_Rank
Difference between Row_Number, Rank, Dense_Rank
Syntax and use:
Row_Number
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
ROW_NUMBER ( ) OVER ([<partition_by_clause>] <order_by_clause>)
Rank Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
RANK ( ) OVER ([< partition_by_clause >] < order_by_clause >)
Dense_Rank
Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
DENSE_RANK ( ) OVER ([<partition_by_clause> ] < order_by_clause > )
NTILE
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
NTILE (integer_expression) OVER ([<partition_by_clause>] < order_by_clause >)
Where
<partition_by_clause>
Divides the result set produced by the From clause into partitions to which the Row_Number/ Rank/ Dense_Rank/ Ntile function is applied.
<order_by_clause>
Determines the order in which the Row_Number/ Rank/ Dense_Rank/ Ntile values are applied to the rows in a partition.
We will apply these function on the below customer product table CustProd.
name

Product

cust1

decoder

cust2

cable

cust1

cable

cust2

package

cust3

decoder

cust3

cable

Please see the below snapshot for understanding of these function through example
With partition by product and order by name,
When we use partition by product, then it divides the result on the basis of product, as there are three distinct products then there will be 3 partitions.
After partition, order by name is used, that means, in the partitions Row Number, Rank or Dense Rank will be assigned as per the order of name. Here in the below result we see that rank ,row number and dense rank, all are having same value, It’s because in each partition there are distinct name given, if name would have been repeated for the same product then those records will have same rank and dense rank, but row number would have been same as shown below.
When used order by product instead of name , then we see in the below result that, the Rank and dense Rank were 1, Because we did partition of result by product , that means there will be common product in each partition , and rank and dense rank will also be same for same product.