ANSI and Teradata Function Equivalents - Part 2 - RANK with Grouping

Function : RANK with Groupings

Teradata Syntax: We use the following Teradata syntax perform ranking with groups Following query gives employee with highest salary with department. SELECT EMPLOYEEID,DEPARTMENTNO,SALARY,RANK(SALARY) AS RNK FROM EMPLOYEE GROUP BY DEPARTMENTNO QUALIFY RNK = 1; Employeeid DepartmentNo Salary RNK 9 100 9000 1 124 200 12345.11 1 11 300 11000 1 144 400 12345.11 1 Thus in Teradata Syntax:   Column used by ranking/ordering is specified within brackets.   Ordering is DESC is default. .ie Highest value gets rank 1 GROUP BY clause creates the groupings. This column also forms a major sort sequence.

ANSI syntax: We can get the same result by making use of ANSI syntax as follows: SELECT EMPLOYEEID,DEPARTMENTNO,SALARY, RANK() OVER (PARTITION BY DEPARTMENTNO ORDER BY SALARY DESC) AS RNK FROM EMPLOYEE QUALIFY RNK = 1; Employeeid DepartmentNo Salary RNK 9 100 9000 1 124 200 12345.11 1 11 300 11000 1 144 400 12345.11 1 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. PARTITION BY is used to indicate the column to be used for grouping. This column also forms a major sort sequence.

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.