Hi people
I have a table called receipts in a SQL Server Database. I want a list of clients, their last receipts (according to date) and the details of these receipts.
ie: If there are 100 different clients in the table (each with 10 receipts) then I should only get 100 records returned.
The receipts table has the following fields
Client ID No (text)
Receipt No (text)
date (date)
amount (currency)
reference (text)
If I use
SELECT [Client Id No], MAX(Date) AS LastReceipt FROM Receipts GROUP BY [Client Id No];
I get the last date for each client. I Still need the amount and reference for that receipt.
If I try and add MAX(Amount) then I get the highest amount independant of the receipt with the highest date. If I just add the amount field (without the MAX) then I get every single receipt in the table.
Anyone know how to do this ?
I have a table called receipts in a SQL Server Database. I want a list of clients, their last receipts (according to date) and the details of these receipts.
ie: If there are 100 different clients in the table (each with 10 receipts) then I should only get 100 records returned.
The receipts table has the following fields
Client ID No (text)
Receipt No (text)
date (date)
amount (currency)
reference (text)
If I use
SELECT [Client Id No], MAX(Date) AS LastReceipt FROM Receipts GROUP BY [Client Id No];
I get the last date for each client. I Still need the amount and reference for that receipt.
If I try and add MAX(Amount) then I get the highest amount independant of the receipt with the highest date. If I just add the amount field (without the MAX) then I get every single receipt in the table.
Anyone know how to do this ?