Max or Last

[Brig Siton]

Hello.

I have a database that have a control called case. Each case have multiple
events. These events have date stamp on them.

What I am trying to find out is how can I show only the most recent event on
each case?

I tried max or last aggregate functions but to no avail.

Help!.

Thank you.

Brigham

 

[Tom Ellison]

Dear Brig:

The MAX() aggregate is definitely the one. If you group by one or more
columns, thereby defining numerous subsets of all the rows by those values,
the the MAX() of the date/time column will give you the most recent
date/time of all the rows in that subset.

If you want to see other columns from the row that has that value of the
date/time, then you would need to use a subquery (or a "domain" function) to
find that row. If this is the case, I need more details in order to help.
You should post a query that show all these columns. Indicate which
column(s) are to be grouped, and which column is the date/time value. I'll
try to do the rest.

Tom Ellison

 

[Brig Siton]

Thank you very much for your prompt answer.

Here are the items:

Tables:
CaseMaster
CaseEvents

Fields:
From Case Master:
CaseNumber
CaseContact
CaseOpenDate

From CaseEvents:
EventDescription
EventDate

I grouped everything and use Max on the EventDate and Group By on all the
other fields but the query still return ALL events per case.

Let me know what I am doing wrong.

Thank you.

Brigham

 

[Tom Ellison]

Dear Brig:

It is certain that the query will return all the values of columns you GROUP
BY. You only want to GROUP BY those columns that determine a level of
summary.

The point is, you cannot return any columns that are not in the GROU)P BY or
in the aggregate. That is, not in an aggregate query. So, you do not want
an aggregate query.

Rather, try this:

SELECT M.CaseNumber, M.CaseContact, M.CaseOpenDate
E.EventDescription, E.EventDate
FROM CaseMaster M
INNER JOIN CaseEvents E
ON E.CaseNumber = M.CaseNumber
WHERE E.EventDate =
(SELECT MAX(E1.EventDate)
FROM EventDate E1
WHERE E1.CaseNumber = M.CaseNumber)

You did not show it, but surely the CaseEvents table has a CaseNumber
column, right?

Except for this question, I believe the above is your solution. No GROUP BY
involved, just filtering.

Tom Ellison

 

[Brig Siton]

Thanks Tom. I got it. it worked.

Dear Brig:

It is certain that the query will return all the values of columns you GROUP
BY. You only want to GROUP BY those columns that determine a level of
summary.

The point is, you cannot return any columns that are not in the GROU)P BY or
in the aggregate. That is, not in an aggregate query. So, you do not want
an aggregate query.

Rather, try this:

SELECT M.CaseNumber, M.CaseContact, M.CaseOpenDate
E.EventDescription, E.EventDate
FROM CaseMaster M
INNER JOIN CaseEvents E
ON E.CaseNumber = M.CaseNumber
WHERE E.EventDate =
(SELECT MAX(E1.EventDate)
FROM EventDate E1
WHERE E1.CaseNumber = M.CaseNumber)

You did not show it, but surely the CaseEvents table has a CaseNumber
column, right?

Except for this question, I believe the above is your solution. No GROUP BY
involved, just filtering.

Tom Ellison