Finding records containing a maximum

[Devonish]

I have a table called Services which contains, among other fields, the
following:
ServiceID
AccountID
ServiceDate

There may be many service records for each account. I wish to select
for
each account the most recent service record.

I can write:
Select AccountID, Max(ServiceDate ) As MaxOfServiceDat e From Services
Group By AccountID

This gives me the most recent ServiceDate for each account BUT how do
I write some SQL which will also give me the ServiceIDs which have these
service dates?


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Peter Doering]

Re: Finding records containing a maximum

> I have a table called Services which contains, among other fields, the[color=blue]
> following:
> ServiceID
> AccountID
> ServiceDate[/color]
[color=blue]
> There may be many service records for each account. I wish to select
> for
> each account the most recent service record.[/color]
[color=blue]
> I can write:
> Select AccountID, Max(ServiceDate ) As MaxOfServiceDat e From Services
> Group By AccountID[/color]
[color=blue]
> This gives me the most recent ServiceDate for each account BUT how do
> I write some SQL which will also give me the ServiceIDs which have these
> service dates?[/color]

SELECT AccountID, ServiceID, ServiceDate
FROM Services
WHERE ServiceDate=(SE LECT Max(S2.ServiceD ate)
FROM Services AS S2 WHERE S2.AccountID=Se rvices.AccountI D);

(not tested)

HTH - Peter

--
No mails please.
This posting is provided AS IS with no warranties, and confers no rights.