MAX and MAX OVER PARTITION BY produces error 3504 in Teradata Query

Question

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

User · Answer

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

User · Answer

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

User · Answer

As Ponies says in a comment  you cannot mix OLAP functions with aggregate functions   Perhaps it s easier to get the last completion date for each employee  and join that to a dataset containing the last completion date for each of the three targeted courses     This is an untested idea that should hopefully put you down the right path     SELECT employee number           course code           MAX course completion date  AS max date           lcc LAST COURSE COMPLETED     FROM employee course completion ecc          LEFT JOIN                SELECT employee number                      MAX course completion date  AS LAST COURSE COMPLETED                FROM employee course completion               WHERE course code IN   M910303    M91301R    M91301P              lcc          ON lcc employee number   ecc employee number    WHERE course code IN   M910303    M91301R    M91301P   GROUP BY employee number  course code  lcc LAST COURSE COMPLETED

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to aggregate-functions

Examples related to teradata

Examples related to database-partitioning