0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.