Problem in finding the latest record

Analytics
Enthusiast

Problem in finding the latest record

Hi,

We are facing this problem in our project :

There are 2 tables tableA and tableB. We need to fetch the code value from the latest row in the tableA which satisfies the join conditions between tableA and tableB. The query is below :

SELECT
A.CODE
FROM
DB1.TABLEA A,
DB1.TABLEB B
WHERE
A.KEYFLD=B.KEYFLD
AND A.FIELD1='001'
AND A.IND1 = 'Y'
AND A.CODE <> ' '
GROUP BY 1
HAVING
A.INSERT_TS = MAX(A.INSERT_TS) ;

But i am getting the error message "3504:Selected Non-Aggregate values must be a part of associate group' .

I tried using a derived table to fetch the max timestamp and the key field by having the same query. then joining with the same source table to fetch the rows.

SELECT
SRC.CODE
FROM
DB1.TABLEA SRC,
(
SELECT
CODE,KEYFLD,MAX(INSERT_TS) AS MAXTS
FROM
DB1.TABLEA A,
DB1.TABLEB B
WHERE
A.KEYFLD=B.KEYFLD
AND A.FIELD1='001'
AND A.IND1 = 'Y'
AND CODE <> ' '
GROUP BY 1,2
) DERIVED
WHERE
SRC.KEYFLD=DERIVED.KEYFLD
AND SRC.FIELD1='001'
AND SRC.IND1 = 'Y'
AND SRC.CODE = DERIVED.CODE
AND A.INSERT_TS=DERIVED.MAXTS;

The above query works fine. But i am not able understand whats wrong with the first query. Please help me understand the problem.

Thanks,
Arun

2 REPLIES
Enthusiast

Re: Problem in finding the latest record



Hi Arun,

In the first query you are not calculating Max(A.INSERT_TS) over CODE,KEYFLD columns. Teradata should know on what level of granularity should max(Insert_Ts) be calculated?
Enthusiast

Re: Problem in finding the latest record

I believe in your first query you need to include A.INSERT_TS in your GROUP BY statement.