I am fairly new to SQL and am trying to create a query. I have the following table
Employee_Branch
Employee_ID Branch_ID Role Start_Date
-----------------------------------------------------------------
100 10 Manager 12/9/2016
200 20 Manager 12/9/2016
300 10 Cashier 1/31/2018
400 10 Cashier 1/31/2018
500 20 Janitor 1/31/2018
600 10 Cashier 1/31/2019
From this table, I want to be able to view the managers of branches with the longest serving employee (the same value as the minimum Start_Date value). Managers themselves are excluded from counting as the longest serving employee. There can be multiple managers per branch
From the data shown above, the needed result is
Result I Need
Employee_ID Branch_ID Role
----------------------------------------
100 10 Manager
200 20 Manager
Because there are two branches which contain the oldest Start_Date value, the managers for both branches are outputted.
If the data changed to
Employee_ID Branch_ID Role Start_Date
-----------------------------------------------------------------
100 10 Manager 12/9/2016
200 20 Manager 12/9/2016
300 10 Cashier 1/31/2018
400 10 Cashier 1/31/2018
500 20 Janitor 1/31/2019
600 10 Cashier 1/31/2019
The output would result in
Employee_ID Branch_ID Role
----------------------------------------
100 10 Manager
Because the minimum Start_Date is only found in branch 10, all managers assigned to branch 10 are outputted. In this case, there is only 1 manager assigned to branch 10.
What I Have Tried
I attempted to implement the following query as a sub query
SELECT DISTINCT Employee_Branch.Branch_ID, Min(Employee_Branch.Start_Date) AS MinOfStart_Date
FROM Employee_Branch
WHERE Employee_Branch.Role <> "Manager"
GROUP BY (Employee_Branch.Branch_ID);
However, this resulted in outputting the minimum Start_Date for each branch as opposed to all branches that contain the minimum Start_Date.
