CODEWITHSUNDEEP
oracle-database
Ash S
Ash S

Reputation: 119

MAX returning ORA-00937: not a single-group group function

My code below joins 3 tables & I would like to return the latest comment of a student (that's why I'd like to use MAX).

select STUDENTTABLE1.STUDENTTABLE1_ID AS "Student ID"
    , STUDENTTABLE1.STUDENTTABLE1_LAST_NAME AS "Last Name"
    , STUDENTTABLE1.STUDENTTABLE1_FIRST_NAME AS "First Name"
    , TABLE2.TABLE2_DELI_CODE AS "Delinquency Code"
    , DBMS_LOB.substr(TABLE3_COMMENT) AS "Comment"
    , TABLE3_ACTIVITY_DATE AS "Comment date"
    , MAX(TABLE3_ACTIVITY_DATE) as Latest_TABLE3_ACTIVITY_DATE --LINE IN QUESTION
    , TABLE3_CREATED_BY AS "Commented by"
    , TABLE2.TABLE2_ACTIVITY_DATE
FROM TABLE2
inner join STUDENTTABLE1
    ON STUDENTTABLE1.STUDENTTABLE1_PIDM = TABLE2.TABLE2_PIDM
inner join TABLE3
    ON TABLE3.TABLE3_PIDM = STUDENTTABLE1.STUDENTTABLE1_PIDM
where STUDENTTABLE1_CHANGE_IND IS NULL
    and TABLE3_ORIG_CODE = 'SectionA'
    and STUDENTTABLE1_ID IN ('101', '213', '141', '123', '103')

The code works without the MAX statement, but with the MAX line, I get the error below

ORA-00937: not a single-group group function

I've tried using GROUP BY in the end below

GROUP BY STUDENTTABLE1.STUDENTTABLE1_FIRST_NAME
    , STUDENTTABLE1.STUDENTTABLE1_ID
    , TABLE2.TABLE2_DELI_CODE
    , TABLE3_ACTIVITY_DATE
    , MAX(TABLE3_ACTIVITY_DATE)
    , TABLE3_CREATED_BY
    , TABLE2.TABLE2_ACTIVITY_DATE
    , TABLE3_CREATED_BY
    , TABLE2.TABLE2_ACTIVITY_DATE

But I get an error

ORA-00934: group function is not allowed here

Upvotes: 0

Views: 49

Answers (1)

MT0
MT0

Reputation: 167972

You can use a LATERAL join to TABLE3 and within that, filter to only include the latest comment and then you do not need to use GROUP BY:

select s.STUDENTTABLE1_ID AS "Student ID"
     , s.STUDENTTABLE1_LAST_NAME AS "Last Name"
     , s.STUDENTTABLE1_FIRST_NAME AS "First Name"
     , t2.TABLE2_DELI_CODE AS "Delinquency Code"
     , DBMS_LOB.substr(t3.TABLE3_COMMENT) AS "Comment"
     , t3.TABLE3_ACTIVITY_DATE AS "Comment date"
     , t3.TABLE3_CREATED_BY AS "Commented by"
     , t2.TABLE2.TABLE2_ACTIVITY_DATE
FROM   TABLE2 t2
       inner join STUDENTTABLE1 s
       ON s.STUDENTTABLE1_PIDM = t2.TABLE2_PIDM
       CROSS JOIN LATERAL (
         SELECT TABLE3_COMMENT,
                TABLE3_ACTIVITY_DATE,
                TABLE3_CREATED_BY
         FROM   TABLE3 t3
         WHERE  t3.TABLE3_ORIG_CODE = 'SectionA'
         AND    t3.TABLE3_PIDM = s.STUDENTTABLE1_PIDM
         ORDER BY TABLE3_ACTIVITY_DATE DESC
         FETCH FIRST ROW ONLY
       ) t3
where  s.STUDENTTABLE1_CHANGE_IND IS NULL
and    s.STUDENTTABLE1_ID IN ('101', '213', '141', '123', '103')

If you did want to use GROUP BY then include the primary keys in the group and use KEEP (DENSE_RANK LAST ...) to get the relevant columns from TABLE3:

select s.STUDENTTABLE1_ID AS "Student ID"
     , MAX(s.STUDENTTABLE1_LAST_NAME) AS "Last Name"
     , MAX(s.STUDENTTABLE1_FIRST_NAME) AS "First Name"
     , t2.TABLE2_DELI_CODE AS "Delinquency Code"
     , t2.TABLE2_ACTIVITY_DATE
     , MAX(DBMS_LOB.substr(t3.TABLE3_COMMENT))
         KEEP (DENSE_RANK LAST ORDER BY t3.TABLE3_ACTIVITY_DATE)
         AS "Comment"
     , MAX(t3.TABLE3_ACTIVITY_DATE) as Latest_TABLE3_ACTIVITY_DATE
     , MAX(t3.TABLE3_CREATED_BY)
         KEEP (DENSE_RANK LAST ORDER BY t3.TABLE3_ACTIVITY_DATE)
         AS "Commented by"
FROM   TABLE2 t2
       inner join STUDENTTABLE1 s
       ON s.STUDENTTABLE1_PIDM = t2.TABLE2_PIDM
       inner join TABLE3 t3
       ON t3.TABLE3_PIDM = s.STUDENTTABLE1_PIDM
where  STUDENTTABLE1_CHANGE_IND IS NULL
and    TABLE3_ORIG_CODE = 'SectionA'
and    STUDENTTABLE1_ID IN ('101', '213', '141', '123', '103')
GROUP BY
       s.STUDENTTABLE1_ID
     , t2.TABLE2_DELI_CODE
     , t2.TABLE2_ACTIVITY_DATE

Upvotes: 1

Related Questions