Hi there! I have two tables:
Customer (ID, Name)
Invoice (ID, Date, Customer, Amount)
and want to select only the *last* invoice for each customer. It's easy
to get each customer's last invoice date:
SELECT Customer.Name, MAX(Invoice.Date)
FROM Customer INNER JOIN Invoice ON Custimer.ID = Invoice.Customer
GROUP BY Customer.Name
but I run into trouble when I try to include the Invoice.Amount. How to
do this in one SQL statement? I came up with:
SELECT Customer.Name, Invoice.Date, Invoice.Amount
FROM Customer INNER JOIN Invoice ON Custimer.ID = Invoice.Customer
WHERE Invoice.ID IN
(
SELECT MID(key, INSTR(key, ":") + 1) FROM
(
SELECT Customer, MAX(CLNG(Invoice.Date) & ":" & CLNG(Invoice.ID))
AS key
FROM Invoice
GROUP BY Customer
)
)
which leaves me somewhat unsatisfied. (And it won't work if the same
customer is invoiced twice on the same day.) There's got to be a more
elegant way to do this. Any ideas?