ANSI and Teradata Function Equivalents - Part 1- RANK

Each Teradata OLAP function that we have seen have ANSI equivalents.

ANSI equivalents are sometimes tedious to write.

Following Table shows the Teradata OLAP functions and its ANSI equivalent.

Function: RANK

Teradata  Syntax: Following is how we write the Teradata RANK function: SELECT EMPLOYEEID,SALARY, RANK (SALARY) FROM EMPLOYEE; Employeeid Salary Rank(Salary) 144 12345.11 1 124 12345.11 1 12 12000 3 11 11000 4 10 10000 5 9 9000 6 8 8000 7 7 7000 8 6 6000 9 5 5000 10 4 4000 11 3 3000 12 2 2000 13 1 1000 14 The Column name in the brackets indicate the column to be  used for ranking and ordering data. In this syntax the highest value gets rank 1 by default. Thus in Teradata Syntax: Column used by ranking/ordering is specified within brackets.   Ordering is DESC is default. .ie Highest value gets rank 1

ANIS Syntax: Following is how we write the RANK function in ANSI: SELECT EMPLOYEEID,SALARY,RANK() OVER (ORDER BY SALARY DESC) FROM EMPLOYEE;   Employeeid Salary Rank(Salary) 144 12345.11 1 124 12345.11 1 12 12000 3 11 11000 4 10 10000 5 9 9000 6 8 8000 7 7 7000 8 6 6000 9 5 5000 10 4 4000 11 3 3000 12 2 2000 13 1 1000 14 The column specified by ORDER BY indicates the column to be used for ranking. Note the word DESC. DESC indicates that ordering is Descending .ie highest value gets 1st rank. Note that this is by default in Teradata syntax, but not in ANSI syntax. So we need to add DESC in the syntax. if we don't add DESC, then default in ANSI syntax is ASC, which means the lowest value gets rank 1 and highest value gets highest rank. Ex: SELECT EMPLOYEEID,SALARY,RANK() OVER (ORDER BY SALARY/*BY DEFAULT ORDERING IS ASC*/) FROM EMPLOYEE; Employeeid Salary Rank(Salary ASC) 1 1000 1 2 2000 2 3 3000 3 4 4000 4 5 5000 5 6 6000 6 7 7000 7 8 8000 8 9 9000 9 10 10000 10 11 11000 11 12 12000 12 144 12345.11 13 124 12345.11 13 Thus in ANSI syntax: Column to be used by ordering is specified by ORDER BY clause. Ordering is ASC by default. We need to specify DESC in order to make highest value get rank 1.