GROUP BY with flagged value

user_2102665 0
Viewed 35

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?

ora-00979 oracle sql
2 Answers

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

user_60336010

The HAVING clause will effectively change the LEFT JOIN to an INNER JOIN and you can, since you are taking the maximum of the column you are grouping by and aggregating on that column is irrelevant as there will only be a singular value per group, then move the HAVING comparison to the ON clause of the join without aggregation:

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;

You could also use a DISTINCT (or UNIQUE) clause instead of the GROUP BY clause.

user_15092640
Related