Aggregate Functions and Window Functions Question
Can you explain why these two sets of code give different results please?
SELECT
de.emp_no, de.dept_no, de.from_date, de.to_date
FROM
dept_emp de
JOIN
(SELECT
emp_no, MAX(from_date) AS from_date
FROM
dept_emp
GROUP BY emp_no) de1 ON de1.emp_no = de.emp_no
WHERE
de.to_date < '2002-01-01'
AND de.from_date > '2000-01-01'
AND de.from_date = de1.from_date;
SELECT
emp_no, dept_no, MAX(from_date) AS from_date, to_date
FROM
dept_emp
WHERE
to_date < '2002-01-01'
AND from_date > '2000-01-01'
GROUP BY emp_no;
The second query fails with 1055 code
Hi Cyd-Marie and Nick!
Thanks for reaching out!
Please, refer to the resources to the following lecture. It is explained the difference between both queries. Also, there is a solution to the error message 1055. Some configurations should be set to resolve the issue. Thank you!
https://learn.365datascience.com/courses/sql/join-and-where-used-together/
Hope this helps.
Best,
Martin