Difference between RANK and ROW_NUMBER in Teradata

by

In Teradata , OLAP window functions play an important role while handling complex SQL requirements. There are various WINDOW functions available in Teradata. However two of the 'tricky' OLAP Window functions available in Teradata are : RANK and ROW_NUMBER.

To know more about ROW_NUMBER in Teradata read this post. So what is the difference between ROW_NUMBER and RANK in Teradata ? The function RANK in teradata resembles very much to rank we have in real life. We tend to give ranks to entities on the basis of values in some columns. However ROW_NUMBER in Teradata is used to generate SEQUENCE number etc. Two records can have same RANK however no two rows can have same row_number within the same partition.

Let us take an example . In this we have a Table Student which has Student Name and Marks obtained by a Student in three Subjects.

We can assign RANK to the students on the basis of total MARKS obtained by a student in all three subjects. The student getting maximum total marks should be given first position. With the help of RANK function we can calculate the RANK of Students.

SELECT
NAME
,SUM(SUB1 + SUB2 + SUB3) AS TOTAL_MARKS
,RANK() OVER (ORDER BY TOTAL_MARKS DESC) AS STUDENT_RANK
FROM STUDENT
GROUP BY 1;

Output given by ROW_NUMBER will be

SELECT
NAME
,SUM(SUB1 + SUB2 + SUB3) AS TOTAL_MARKS
,ROW_NUMBER() OVER (ORDER BY TOTAL_MARKS DESC) AS STUDENT_ROW_NUMBER
FROM STUDENT
GROUP BY 1;

I hope with the help of above example , difference between RANK and ROW_NUMBER can be seen easily. Feel free to drop a comment in case of any queries.

Leave a Comment