Unique names with employee department

Question

I have an employee database and employees are appearing multiple times.

• Some instances of same employee has employee department, where as in some other instances, employee department is missing. For this I need to find distinct of employee name along with employee department.

• Some employees do not have any employee department at all. For this I need to only find distinct employee name.

My query:

------for each unique employee, find department
--select columns
select 
  a.employee_name,b.employee_dept
from
  --unique employee names
  (
   select 
     distinct employee_name 
   from 
     employeeDB 
   where 
     employee_name is not null and employee_name != ''
   order by employee_name asc
  ) as a,
  --employee name and employee dept sorted
  (
  select 
     employee_name,employee_dept 
   from 
     employeeDB 
   where employee_name is not null and employee_name != ''
   order by employee_name asc, employee_dept desc
  ) as b
--condition for join
where
  a.employee_name = b.employee_name
order by a.employee_name asc, b.employee_dept desc
limit 1000

I am not sure why results are not returning unique results. i am getting results similar to below

Name  Dept
A     DepA
A     DepB
A   
B     DepA
B   
C     DepZ
D   
E   

Answer 1

This will get all unique names (by grouping it) and grabs the (alphabetically) highest employee_dept it can find for each

select employee_name, max(employee_dept) as dept
from employeeDB 
where employee_name is not null and employee_name != ''
group by employee_name
order by employee_name asc
limit 1000