Most recent date

[Guest]

There has got to be an easy way of doing this, so I thought I would throw it
out there and see as I am drawing a blank.

I am trying to write a query that call for a list of accounts, a date and
other info. For each account I need it to only return the record with the
most recent date field.

Anyone have an idea how to most easily accoplish this? Lets say my table
looks like

Account# CustName TransactionDate

And I want to return all 3 fields for every customer, ommitting duplicated,
but including only the most recent date.

Thanks in advance.

Rick

 

[Guest]

That sounds about the same as I needed and just got the following from a
wonderful person! Hope it Helps
Hi

I have an aggregate query which groups on product codes, then uses Max to
find the latest invoice date. I then want to see the amount recorded against
the latest invoice but can't figure out how to do it. This figure will not
necessarily be the highest value so can't use Max again. Any help much
appreciated. Thought this would be a 2 minute query to create!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT product_code, invoice_date, amount
FROM table_name As t
WHERE invoice_date = (SELECT Max(invoice_date)
FROM table_name
WHERE product_code = t.product_code)

Substitute your table's name for "table_name" and your column names for
my column names.

 

[Guest]

Thanks, this was exactly what I was looking for!

Have a great weekend!

Rick