Resolved: Using MAX() for de.dept_no

Hello,

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,
Carl