I know this is a very old question, but I've been asked by someone else something similar.
I don't have TeraData, but can't you do the following?
SELECT employee_number,
course_code,
MAX(course_completion_date) AS max_course_date,
MAX(course_completion_date) OVER (PARTITION BY employee_number) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code
The GROUP BY
now ensures one row per course per employee. This means that you just need a straight MAX()
to get the max_course_date
.
Before your GROUP BY
was just giving one row per employee, and the MAX() OVER()
was trying to give multiple results for that one row (one per course).
Instead, you now need the OVER()
clause to get the MAX()
for the employee as a whole. This is now legitimate because each individual row gets just one answer (as it is derived from a super-set, not a sub-set). Also, for the same reason, the OVER()
clause now refers to a valid scalar value, as defined by the GROUP BY
clause; employee_number
.
Perhaps a short way of saying this would be that an aggregate
with an OVER()
clause must be a super-set of the GROUP BY
, not a sub-set.
Create your query with a GROUP BY
at the level that represents the rows you want, then specify OVER()
clauses if you want to aggregate at a higher level.