All Forums Analytics
dneaster3 1 post Joined 06/10
22 Jun 2010
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?

Adeel Chaudhry 773 posts Joined 04/08
22 Jun 2010

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

-- If you are stuck at something .... consider it an opportunity to think anew.

Jimm 298 posts Joined 09/07
23 Jun 2010

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
;
---------------------------------------------

sqllion 2 posts Joined 07/10
23 Jul 2010

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/

sam_dhse 11 posts Joined 07/10
30 Jul 2010

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

Ghalia 11 posts Joined 12/15
20 Jul 2016

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

You must sign in to leave a comment.