Cheeky Charlie
New member
- Local time
- Today, 09:02
- Joined
- Dec 29, 2009
- Messages
- 9
AKA 'another subquery question'
I know the answer is almost certainly here, but I've been wrestling for a long time and I'm stuck. If someone wouldn't mind showing me how to make it work with my specific tables I'd be really grateful:
I have a table of Cases and a table of Events, each Case has 0-many Events. I want to show all Cases with the latest Event for each Case.
I have:
(I've stripped out extraneous fields, but would like to include things like t_Cases.CustomerName and t_Events.Description but I don't think these are necessary for the question and I'm trying to keep it clear for me and everyone else - please tell me if I'm being an idiot).
If I do this:
I still get just as many results - as I'm grouping by a series of unique entries (Event_ID).
If I do this:
I get the maximum Event number, which is often not the latest chronologically...
Any help would be much appreciated!
CC
I know the answer is almost certainly here, but I've been wrestling for a long time and I'm stuck. If someone wouldn't mind showing me how to make it work with my specific tables I'd be really grateful:
I have a table of Cases and a table of Events, each Case has 0-many Events. I want to show all Cases with the latest Event for each Case.
I have:
Code:
SELECT t_Cases.Case_ID, t_Events.Event_ID, t_Events.Date
FROM t_Cases INNER JOIN t_Events ON t_Cases.ID = t_Events.Case_ID;
If I do this:
Code:
SELECT t_Cases.Case_ID, t_Events.Event_ID, Max(t_Events.Date) AS MaxOfDate
FROM t_Cases INNER JOIN t_Events ON t_Cases.Case_ID = t_Events.Case_ID
GROUP BY t_Cases.Case_ID, t_Events.Event_ID;
If I do this:
Code:
SELECT t_Cases.Case_ID, Max(t_Events.Event_ID) AS MaxOfEvent_ID, Max(t_Events.Date) AS MaxOfDate
FROM t_Cases INNER JOIN t_Events ON t_Cases.Case_ID = t_Events.Case_ID
GROUP BY t_Cases.Case_ID;
Any help would be much appreciated!
CC
Last edited: