Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Wednesday, 25 June 2014

Difference between Row_Number, Rank, Dense_Rank Functions

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.