MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504

Analytics

MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504

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?
5 REPLIES
Teradata Employee

Re: MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504

Hi,

What you are trying to do is to get two MAX() values, one partitioned other over whole data-set. As semantically it is wrong, so you need to re-valid what exactly you are trying to do here.

Regards,

MAC
Enthusiast

Re: MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504

The query below using derived tables will give you what you asked for, but it is probably not what you want!
------------------------------------------------
Select D1.employee_number, D1.max_date, D2.max_course_date
From
(SELECT employee_number,
MAX(course_completion_date) AS max_date
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number) D1
Join
(SELECT employee_number,
MAX(course_completion_date) OVER (PARTITION BY course_code) AS max_course_date,
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
) D2
On D1.employee_number = D2.Employee_Number
;

---------------------------------------------------
If the employee did the 3 courses, you get 3 rows and no indication of what course relates to what course completion date.
The following query gives you a more meaningful result:

----------------------------------------------------------
SELECT employee_number,
MAX(course_completion_date) AS max_date
MAX(CASE WHEN Course_Code = 'M910303' THEN course_completion_date END) As max_M910303_date,
MAX(CASE WHEN Course_Code = 'M9103R' THEN course_completion_date END) As max_M9103R_date,
MAX(CASE WHEN Course_Code = 'M9103P' THEN course_completion_date END) As max_M9103P_date,
FROM employee_course_completion
WHERE course_code IN ('M910303', 'M91301R', 'M91301P')
GROUP BY employee_number
;
---------------------------------------------

Re: MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504

Dense_Rank returns the rank as positive integers without any gaps in between the ranks. , it will assign the rank to the records as per the condition used in clause. Follow the link to know more…
http://www.sqllion.com/2010/07/dense_rank/
Enthusiast

Re: MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504

I don't think there is a function as dense_rank in teradata.Its there in Oracle , not in teradata.
Enthusiast

Re: MAX() and MAX() OVER (PARTITION BY ....) in the same query produces error 3504

Maybe you just need to group also by course_code ( the field in the partition part)...