Rank vs Row_Number in Teradata

Rank vs Row_Number in Teradata

Anyone working on Teradata would be aware of the two most common OLAP functions in Teradata which are used for common use i.e.

1.      Rank()

2.      Row_Number()

  In one of my posts I have mentioned about how to generate sequence number (ex. Surrogate Key) using Row_Number.   Today let us find out the difference in usage of rank and row number functions by taking following scenario on tables (ProductSales)

SELECT    Product, DateOfSale, SalesAmount,

RANK() OVER (ORDER  BY SalesAmount DESC)  ,  -- RANK function

ROW_NUMBER() OVER (ORDER     BY SalesAmount DESC) -- ROW_NUMBER function

FROM   ProductSales

The result set is as follows:

In the result set, we can observe the following,  

1.       Rows 1 and 2 have same rank assigned  since that have same SalesAmount value

2.       Rows 1 and 2 have different row numbers assigned even though same SalesAmount value

3.       Row 3 has rank “3” assigned as ranking positions

Hence , rows are always assigned an incremented sequence number with ROW_NUMBER.

Rows are always assigned the same number with RANK function.