1789. Primary Department for Each Employee.md

1789. Primary Department for Each Employee

Question Link

Solution v1

# Write your MySQL query statement below
select employee_id, department_id from Employee group by employee_id having count(department_id)=1
union
select employee_id, department_id from Employee
where primary_flag='Y'

Solution v2: count() over(partition by)

select t.employee_id, t.department_id from
(select employee_id, department_id, primary_flag, count(department_id) over(partition by employee_id) as cnt from Employee) as t
where t.cnt = 1 or t.primary_flag = 'Y'

• count() is what you want to do
• over(partition by) is where to do, the range or the field of count()
• the overall meaning is: perform count(department_id) function to every employee_id
• comparing to group by: it keeps every line. group by will combine every line