Resolved: Using MAX() for de.dept_no
I've quite understand the use of MIN() for pulling employee dept_no. For that reason, I checked the dept_emp table if there exist employees with multiple dept_no, and my query returned 31579 unique records where an employee has two department codes (no employees have more than two dept_no).
Then using the subqueries, I was able to pull the information of those emp_no using the query:
SELECT * FROM dept_emp WHERE emp_no IN ( SELECT a.emp_no FROM ( SELECT emp_no, COUNT(dept_no) AS dept_no FROM dept_emp GROUP BY emp_no HAVING dept_no > 1) a);
Now here's my concern. Looking at the from_date and to_date, it tells that the most recent dept_no of these employees are on the latter. Hence, it would make the latter dept_no their current dept_no, and it would make more sense to use MAX() instead, right? (I think all of them transferred to a department with higher dept_no than their former one for some reason)
Thanks for reaching out.
Yes, you can use an alternative solution. Using the MAX() function here would follow the same logic therefore it will serve identical purpose. It is just a matter of our decision which one to choose.
Hope this helps.