Difference between Rank and Row_number() functions in teradata

 

RANK function in teradata resembles very much to rank we have in real life. 

for example,Students with equal number of marks are assigned with same rank.

ROW_NUMBER function in Teradata is used to generate SEQUENCE number

Using Rank function:

SELECT

Student_name

,SUM(English + Maths) AS MARKS_Total

,RANK() OVER (ORDER BY MARKS_Total DESC) AS STUDENT_RANK

FROM STUDENT

GROUP BY 1;

Output of above sql:

Student_name MARKS_Total Student_ROWNUM

John                       198                     1

David                     165                     3

Alice                      165                      3

Using ROW_NUMBER function:

SELECT

Student_name

,SUM(English + Maths) AS MARKS_Total

,ROW_NUMBER() OVER (ORDER BY MARKS_Total DESC) AS STUDENT_ROWNUM

FROM STUDENT

GROUP BY 1;

Output of above sql:

Student_name MARKS_Total Student_ROWNUM

John                       198                     1

David                     165                      2

Alice                      165                      3