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.