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.
Please provide your feedback for the post, if you find this post useful.
Thx Ragini, I really enjoyed your post! I also came across this other useful one:
ReplyDeletedifference between rank and dense rank
Nice to compare all three at one place. This gives better understanding.
ReplyDeleteThank you so much. Doing good job
ReplyDeletevery good
ReplyDeleteWell explained
ReplyDelete~Nebu
Very gud explanation Ragini,,,!!!
ReplyDeleteNithya
Good Explanation!!
ReplyDeleteNo big Talks, all the examples have been well illustrated and clearly explained. Thanks for your efforts. Keep up the Good work.
ReplyDelete