CODEWITHSUNDEEP
sqloracle-database
Sulejmani
Sulejmani

Reputation: 11

How to get results of rows with no values in MAX clause

I have following SQL to execute:

SELECT ACCT_GRP_CODE, MAX(RECEPTION_TIMES) AS RECEPTION_TIMES
FROM VIEW_ID_MONITORING
WHERE SYSTIMESTAMP AT TIME ZONE 'UTC'>FROM_TZ(CAST(TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || RECEPTION_END, 'YYMMDDHH24:MI') AS TIMESTAMP), UTC_TIME_TOTAL_OFFSET)
GROUP BY ACCT_GRP_CODE

This calculates the highest reception time in the past. However this only returns data for account groups that have reception times in the past. I also want to return the account group codes without reception times in the past.

How should I do that?

Upvotes: 0

Views: 50

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

One method is conditional aggregation:

SELECT ACCT_GRP_CODE,
       MAX(CASE WHEN SYSTIMESTAMP AT TIME ZONE 'UTC'>FROM_TZ(CAST(TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || RECEPTION_END, 'YYMMDDHH24:MI') AS TIMESTAMP), UTC_TIME_TOTAL_OFFSET) THEN RECEPTION_TIMES
           END) AS RECEPTION_TIMES
FROM VIEW_ID_MONITORING
GROUP BY ACCT_GRP_CODE;

If you have a list of the account group codes, you can do:

SELECT agc.ACCT_GRP_CODE, MAX(vim.RECEPTION_TIMES) AS RECEPTION_TIMES
FROM ACCOUNT_GROUP_CODES agc LEFT JOIN
     VIEW_ID_MONITORING vim
     ON agc.ACCT_GRP_CODE = vim.ACCT_GRP_CODE AND
        SYSTIMESTAMP AT TIME ZONE 'UTC'>FROM_TZ(CAST(TO_DATE(TO_CHAR(SYSDATE, 'YYYYMMDD') || RECEPTION_END, 'YYMMDDHH24:MI') AS TIMESTAMP), UTC_TIME_TOTAL_OFFSET)
GROUP BY agc.ACCT_GRP_CODE;

Upvotes: 3

Related Questions