Last answered:

09 Sept 2022

Posted on:

08 Sept 2022


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:

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)


1 answers ( 1 marked as helpful)
Posted on:

09 Sept 2022


Hi Carl!
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.

Submit an answer