Teradata ROW_NUMBER

Function : ROW_NUMBER

ROW_NUMBER is ANSI SQL-99 Function. ROW_NUMBER is same as RANK except in the event of a tie. ROW_NUMBER does not report duplicate values ,unlike RANK.   Following is the result of the RANK function: SELECT EMPLOYEEID,departmentno,salary,RANK() OVER (ORDER BY salary desc) from employee2; Employeeid DepartmentNo Salary Rank(Salary) 124 200 12345.11 1 144 400 12345.11 1 12 400 12000 3 11 300 11000 4 10 200 10000 5 9 100 9000 6 8 400 8000 7 7 300 7000 8 6 200 6000 9 5 100 5000 10 4 400 4000 11 3 300 3000 12 2 200 2000 13 1 100 1000 14 Note that ties produce the same ranking number however their positions are counted against subsequent rankings. Following is the result of the using ROW_NUMBER: SELECT EMPLOYEEID,departmentno,salary,ROW_NUMBER() OVER (ORDER BY salary desc) from employee2; Employeeid DepartmentNo Salary Row_Number() 144 400 12345.11 1 124 200 12345.11 2 12 400 12000 3 11 300 11000 4 10 200 10000 5 9 100 9000 6 8 400 8000 7 7 300 7000 8 6 200 6000 9 5 100 5000 10 4 400 4000 11 3 300 3000 12 2 200 2000 13 1 100 1000 14 Note that even case of tie ROW_NUMBER assigns values sequentially.