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?
解决方案
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
7677
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
