Hi All,

I need to write an SQL that will return the last contact for each client.
One table has the clients in it and another has the contacts that happen for
each client linked by the ClientID (integer field). I just need to return
the last contact by date.
So each client might have 10 contacts, but I only want to return the client
and the contact with the latest date.

Any idea how this SQL can be wriiten.

Cheers,
Paul

Paul,

<< I need to write an SQL that will return the last contact for each
client. One table has the clients in it and another has the contacts
that happen for each client linked by the ClientID (integer field). I
just need to return the last contact by date.>>

If what you want is a list containing the last contact for each client,
where clients with no contacts or where contact date is null will not be
in the list, then:

SELECT
  *
FROM
  Contacts C
WHERE
  ContactDate = (SELECT MAX(ContactDate)
                 FROM Contacts
                 WHERE ClientID = C.ClientID)


However, if what you need is to get the date of the last contact along
with each client data, then:

SELECT
  *,
  (SELECT MAX(ContactDate)
   FROM Contacts
   WHERE ClientID = C.ClientID) AS LastContact
FROM
  Clients C

In this case if there are no contacts for a client or all contact dates
are NULL, then the client will still be in the list but LastContact will
be NULL.


--
Fernando Dias
[Team Elevate]

Hi Fernando,

excellent. Works like a charm. Thanks so much for the help.

Cheers,
Paul

"Fernando Dias" <fernandodias.removthis@easygate.com.pt> wrote in message
news:4FDEEB8A-0250-4781-B416-335C3B41D176@news.elevatesoft.com...
> Paul,
>
> << I need to write an SQL that will return the last contact for each
> client. One table has the clients in it and another has the contacts that
> happen for each client linked by the ClientID (integer field). I just need
> to return the last contact by date.>>
>
> If what you want is a list containing the last contact for each client,
> where clients with no contacts or where contact date is null will not be
> in the list, then:
>
> SELECT
>   *
> FROM
>   Contacts C
> WHERE
>   ContactDate = (SELECT MAX(ContactDate)
>                  FROM Contacts
>                  WHERE ClientID = C.ClientID)
>
>
> However, if what you need is to get the date of the last contact along
> with each client data, then:
>
> SELECT
>   *,
>   (SELECT MAX(ContactDate)
>    FROM Contacts
>    WHERE ClientID = C.ClientID) AS LastContact
> FROM
>   Clients C
>
> In this case if there are no contacts for a client or all contact dates
> are NULL, then the client will still be in the list but LastContact will
> be NULL.
>
>
> --
> Fernando Dias
> [Team Elevate]
>