[sql] MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query

I am trying to produce a results table with the last completed course date for each course code, as well as the last completed course code overall for each employee. Below is my query:

SELECT employee_number,
       MAX(course_completion_date) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number

This query produces the following error:

3504 : Selected non-aggregate values must be part of the associated group

If I remove the MAX() OVER (PARTITION BY...) line, the query executes just fine, so I've isolated the problem to that line, but after searching these forums and the internet I can't see what I'm doing wrong. Can anyone help?

Logically OLAP functions are calculated after GROUP BY/HAVING, so you can only access columns in GROUP BY or columns with an aggregate function. Following looks strange, but is Standard SQL:

SELECT employee_number,
       MAX(MAX(course_completion_date)) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

And as Teradata allows re-using an alias this also works:

SELECT employee_number,
       MAX(max_date) 
           OVER (PARTITION BY course_code) AS max_course_date,
       MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

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.

SELECT employee_number, course_code, MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number, course_code

I think this will work even though this was forever ago.

SELECT employee_number, Row_Number()  
   OVER (PARTITION BY course_code ORDER BY course_completion_date DESC ) as rownum
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
   AND rownum = 1

If you want to get the last Id if the date is the same then you can use this assuming your primary key is Id.

SELECT employee_number, Row_Number()  
   OVER (PARTITION BY course_code ORDER BY course_completion_date DESC, Id Desc) as rownum    FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
   AND rownum = 1