GROUP BY with analytic functions

Database

GROUP BY with analytic functions

Hello All,

I'm new to Teradata and used to work on oracle. Could someone please tell me how a "group by" clause works when used alongside analytic functions.

I have the following query and I'm confused about the way group by clause works. The problem is that I dont have access to a teradata instance and I'm trying to convert these to Oracle SQL's

SELECT DISTINCT '200812' AS BASE_MONTH
, amth.accs_meth_id
, amth.accs_meth_type_start_dt AS INFLOW_DATE
FROM access_method_type_hist amth

INNER JOIN
access_method am
ON am.accs_meth_id = amth.accs_meth_id

WHERE amth.accs_meth_type_start_dt <= ADD_MONTHS(CAST(base_month AS DATE format 'yyyymm'),1) - 1

GROUP BY amth.accs_meth_id
QUALIFY RANK(amth.accs_meth_type_start_dt DESC) = 1
AND amth.accs_meth_type_cd = 'PRE';

Also could you please tell me why the condition amth.accs_meth_type_cd = 'PRE' has been placed after the qualify clause? Would it make any difference if it is placed before the GROUP BY clause (i.e immediately after the first where condition)?

Appreciate your assistance.

Regards
Krishna
1 REPLY
Senior Apprentice

Re: GROUP BY with analytic functions

Hi Krishna,
this query uses an old deprecated version of RANK.
Rewrite it moving the GROUP BY into a PARTITION BY:

SELECT DISTINCT '200812' AS BASE_MONTH
, amth.accs_meth_id
, amth.accs_meth_type_start_dt AS INFLOW_DATE
FROM access_method_type_hist amth

INNER JOIN
access_method am
ON am.accs_meth_id = amth.accs_meth_id

WHERE amth.accs_meth_type_start_dt <= ADD_MONTHS(CAST(base_month AS DATE format 'yyyymm'),1) - 1

QUALIFY RANK() OVER (PARTITION BY amth.accs_meth_id ORDER BY amth.accs_meth_type_start_dt DESC) = 1
AND amth.accs_meth_type_cd = 'PRE';

"Also could you please tell me why the condition amth.accs_meth_type_cd = 'PRE' has been placed after the qualify clause?"

Because it returns the right answer set.

"Would it make any difference if it is placed before the GROUP BY clause (i.e immediately after the first where condition)?"

Probably, just try it.

Dieter