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

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.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to aggregate-functions

Spark SQL: apply aggregate functions to a list of columns GROUP BY without aggregate function GROUP BY + CASE statement must appear in the GROUP BY clause or be used in an aggregate function Naming returned columns in Pandas aggregate function? Concatenate multiple result rows of one column into one, group by another column How to include "zero" / "0" results in COUNT aggregate? Apply multiple functions to multiple groupby columns Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause Optimal way to concatenate/aggregate strings

Examples related to teradata

What is ROWS UNBOUNDED PRECEDING used for in Teradata? Find which rows have different values for a given column in Teradata SQL MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query SQL SELECT multi-columns INTO multi-variable

Examples related to database-partitioning

Oracle Partition - Error ORA14400 - inserted partition key does not map to any partition MAX() and MAX() OVER PARTITION BY produces error 3504 in Teradata Query