GROUP BY question

Let's say I have an Invoices table setup and I want to find the most recent
invoice date per customer. I'd say:

SELECT Customer, Max(InvoiceDate)
FROM Invoices
GROUP BY Customer

Now what if I want to see the invoice number (or some other field of the
Invoices table) of those most recent invoices? In other words, the above
query tells me the date of the most recent invoice per customer. What if I
wanna know more information about that particular invoice? Do I have to do
a separate query?

You can use that query as a subquery. The SQL statement would look like
the following.

SELECT Invoices.*
FROM Invoices INNER JOIN
(SELECT Customer, Max(InvoiceDate) as Latest
FROM Invoices
GROUP BY Customer ) as x
ON Invoices.Customer = x.Customer
AND Invoices.InvoiceDate = x.Latest


'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


0to60 wrote:
Let's say I have an Invoices table setup and I want to find the most recent
invoice date per customer. I'd say:

SELECT Customer, Max(InvoiceDate)
FROM Invoices
GROUP BY Customer

Now what if I want to see the invoice number (or some other field of the
Invoices table) of those most recent invoices? In other words, the above
query tells me the date of the most recent invoice per customer. What if I
wanna know more information about that particular invoice? Do I have to do
a separate query?