GROUP BY with flagged value-CodePudding

Below query is to return flag as Y if c.LAST_UPDATED_TIMESTAMP < MAX(t.LATEST_ACTION_TIMESTAMP)

SELECT
'Y' as CAN_UPDATE, t.LATEST_ACTION_TIMESTAMP
FROM CUSTOMERS c
LEFT JOIN TRANSACTIONS t ah on (c.customer_id = t.customer_id)
WHERE
t.status_active_flag = 'Y' and  c.customer_ID ='CUST_019'
GROUP BY t.LATEST_ACTION_TIMESTAMP
HAVING c.LAST_UPDATED_TIMESTAMP < MAX(t.LATEST_ACTION_TIMESTAMP);

ORA-00979 not a GROUP BY expression encountered, understand that all columns in SELECT need to be included in GROUP BY clause. How can handle for the flagged value 'Y' in this case?

CodePudding user response：

Column c.LAST_UPDATED_TIMESTAMP need to be added to group by part as well

GROUP BY t.LATEST_ACTION_TIMESTAMP, c.LAST_UPDATED_TIMESTAMP

here is a dbfiddle with a dumb example

CodePudding user response：

The HAVING clause will effectively change the LEFT JOIN to an INNER JOIN and you can then move the HAVING comparison to the ON clause of the join:

SELECT 'Y' as CAN_UPDATE,
       t.LATEST_ACTION_TIMESTAMP
FROM   CUSTOMERS c
       INNER JOIN TRANSACTIONS t
       ON (   c.customer_id = t.customer_id
          AND c.LAST_UPDATED_TIMESTAMP < t.LATEST_ACTION_TIMESTAMP
          )
WHERE  t.status_active_flag = 'Y'
AND    c.customer_id ='CUST_019'
GROUP BY t.LATEST_ACTION_TIMESTAMP;