Teradata FAQ's: ANSI and Teradata Function Equivalents - Part 2

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;

Thus in Teradata Syntax:

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;

Thus in ANSI syntax:

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.

Teradata FAQ's