Last answered:

22 May 2024

Posted on:

20 Feb 2023

0

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;

2 answers ( 0 marked as helpful)
Posted on:

18 Mar 2023

0

The second query fails with 1055 code

Instructor
Posted on:

22 May 2024

0

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

Submit an answer