Last answered:

18 Mar 2023

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;

1 answers ( 0 marked as helpful)
Posted on:

18 Mar 2023

0

The second query fails with 1055 code

Submit an answer