Let's say that I have these tables:
Widgets ID Widget 1 W1 2 W2 Widget_Making ID Person_Involved ID_Widgets 1 John 1 2 James 1 3 John 2 Widget_Making_History ID ID_Widget_Making Start End 1 1 1/1/2019 1/2/2019 2 1 1/3/2019 1/4/2019 3 2 1/5/2019 1/6/2019 4 2 1/7/2019 1/8/2019 5 3 1/9/2019 1/10/2019 Widget_Making.ID_Widgets FK to Widgets.ID Widget_Making_History.ID_Widget_Making FK to Widget_Making.ID
Now, I want to be able to determine the most recent end date (Widget_Making_History.End) grouped by the widget (Widgets.Widget) along with the person involved in making the widget who is associated with the most recent end date value (Widget_Making.Person_Involved).
My query should look like this:
Widget MaxOfEnd Person_Involved W1 1/8/2019 James W2 1/10/2019 John
However, if I drag Person_Involved into the query, I'm forced to group by it, so I would end up getting something like this:
Widget MaxOfEnd Person_Involved W1 1/2/2019 John W1 1/4/2019 John W1 1/6/2019 James W1 1/8/2019 James W2 1/10/2019 John
I really would like to get the previous query without doing something like MAX(Person_Involved). Something akin to "Person_Involved corresponds to MAX(Widget_Making_History.End)" would suffice.
This is the query that I'm currently using:
SELECT Widgets.Widget, Max(Widget_Making_History.End) as MaxOfEnd, Person_Involved FROM Widgets LEFT JOIN Widget_Making LEFT JOIN Widget_Making_History ON Widget_Making_History.ID_Widgets_Making = Widgets_Making.ID ON Widgets_Making.ID_Widgets = Widgets.ID GROUP BY Widgets.Widget, Widget_Making.Person_Involved