Max or Last

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brig Siton - 28 Apr 2006 22:04 GMT

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

Reply to this Message

Tom Ellison - 28 Apr 2006 22:14 GMT

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

> Hello.
>
[quoted text clipped - 13 lines]
>
> Brigham

Reply to this Message

Brig Siton - 28 Apr 2006 22:26 GMT

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

> Dear Brig:
>
[quoted text clipped - 29 lines]
> >
> > Brigham

Reply to this Message

Tom Ellison - 28 Apr 2006 22:55 GMT

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

> Thank you very much for your prompt answer.
>
[quoted text clipped - 61 lines]
>> >
>> > Brigham

Reply to this Message

Brig Siton - 02 May 2006 14:09 GMT

Thanks Tom. I got it. it worked.

> Dear Brig:
>
[quoted text clipped - 91 lines]
> >> >
> >> > Brigham

Reply to this Message