More problems with Max querys

[camelman]

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 ?

 

[swampBoogie]

select * from recipts r
where date in (select max(date) from recipts
where [Client Id No] = r.[Client Id No])

Why don't you get rid of the spaces in the column names? Just causing a bother and to no avail.

 

[sunila7]

Hi,

Have a look at this thread

thread183-491142


Sunil

 

[camelman]

Thanks guys. Works like a charm.