Query to list all accounts with latest date

[rdifolco]

Here is the scoop.

I have 1 table. It is used to list when an account is updated. A new entry
is entered everytime a change is made.

Table Example:

Account Latest_Date Received_by

abc 3/13/2006 rich
abc 3/18/2006 rich
abc 3/15/2006 rich
nbc 3/13/2006 rich
nbc 3/14/2006 rich
nbc 3/19/2006 rich

I need to be able to make a query that will give me a result only displaying
accounts with the latest date:

Example of Result:

Account Latest_Date Received_by

abc 3/18/2006 rich
nbc 3/19/2006 rich

How can I di this.

Any help is appriciated.

 

[Tom Ellison]

Dear RD:

You want one row in the results for each Account, showing the latest
(maximum) date and showing the Received_by that goes with that Account and
date.

A problem with this would be this:

Account Latest_Date Received_by

abc 3/18/2006 rich
abc 3/18/2006 don

In this case, there are two results for abc. Both are for the date
3/18/2006 but have different Received_by.

The query I propose will return both.

SELECT Account, Latest_Date, Received_by
FROM YourTable T
WHERE Latest_Date =
(SELECT MAX(Latest_Date)
FROM YourTable T1
WHERE T.Account = T1.Account)

Tom Ellison

 

[rdifolco]

Worked Great!!

Thank you for your fast response!


~RIch

Dear RD:

You want one row in the results for each Account, showing the latest
(maximum) date and showing the Received_by that goes with that Account and
date.

A problem with this would be this:

Account Latest_Date Received_by

abc 3/18/2006 rich
abc 3/18/2006 don

In this case, there are two results for abc. Both are for the date
3/18/2006 but have different Received_by.

The query I propose will return both.

SELECT Account, Latest_Date, Received_by
FROM YourTable T
WHERE Latest_Date =
(SELECT MAX(Latest_Date)
FROM YourTable T1
WHERE T.Account = T1.Account)

Tom Ellison

Here is the scoop.

[quoted text clipped - 27 lines]

Any help is appriciated.