I need to find the department that has the oldest employee. My plan is to merge everything into one big table and try to sort the employees by age, then find the department that has the oldest employee, and find the manager for that department. Currently this is my query:

  FROM employees emp 
  JOIN dept_emp de       ON emp.emp_no=de.emp_no 
  JOIN departments dep   ON de.dept_no=dep.dept_no 
  JOIN dept_manager depm ON dep.dept_no=depm.dept_no 
  JOIN employees emp2    ON depm.emp_no=emp2.emp_no
-- some part of query is lost here -- 
 LIMIT 0, 1


Unfortunately, when I run that, it's saying that column 'birth_date' is ambiguous to sort - which makes sense bc I'll have 2 columns with the same date.

  • You have the ambiguity because you have joined employees twice and both instances have the same set of columns. Therefore you have to use SELECT emp.*, de.*, dep.* ... instead of plain SELECT *
    – Kondybas
    Commented Oct 14, 2018 at 15:03
  • You seem to have many:many mapping tables in places where you need only 1:many relationships? Or, can an emp be in several depts? And a dept can have several managers?
    – Rick James
    Commented Oct 19, 2018 at 2:52

1 Answer 1


I've rid of the redundant second subselect so now it is the single one that fetch employee(s) having earliest date of birth (highest age), their IDs, names, IDs of their managers and names of managers.

SELECT ds.dept_no
     , ol.emp_no     AS oldest_emp_no
     , ol.birth_date AS oldest_bd
     , e1.first_name AS o_first_name
     , e1.last_name  AS o_last_name
     , dm.emp_no     AS req_manager_no
     , e2.first_name AS m_first_name
     , e2.last_name  AS m_last_name

  FROM departments  AS ds
  JOIN dept_emp     AS de ON de.dept_no = ds.dept_no
  JOIN employees    AS e1 ON e1.emp_no = de.emp_no
  JOIN ( SELECT MIN(birth_date) AS birth_date FROM employees ) 
         AS ol ON ol.birth_date = e1.birth_date
  JOIN dept_manager AS dm ON dm.dept_no = ds.dept_no
  JOIN employees    AS e2 ON e2.emp_no = dm.emp_no
  • Nice one - but, errr...., shouldn't that be MAX(birth_date) and not MIN? :-)
    – Vérace
    Commented Oct 14, 2018 at 16:14
  • @Vérace The higher age - the lower birth date :) If we looking for OLDEST one we need the lowest/earliest date of birth.
    – Kondybas
    Commented Oct 14, 2018 at 18:02
  • Gawd... I must have had the brain in neutral for that one! :-)
    – Vérace
    Commented Oct 14, 2018 at 19:59
  • Maybe I'm missing a couple of hemispheres?
    – Vérace
    Commented Oct 14, 2018 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.