Date max

[jas2803]

I have two tables,
active clients and shipments.
Shipments tables is like this
client, date, amount
the clients table is:
client, state, city
I use this to get the Max amount per client for a time period:

SELECT Max(monthly.amount) as max_amt, clients.client, clients.city FROM clients LEFT OUTER JOIN (SELECT client, date, amount FROM shipment WHERE "date" Between '11/30/08' and '1/1/09') monthly ON clients.client = monthly.client WHERE (clients.state = 'AZ') GROUP BY clients.client, clients.city ORDER BY clients.city

I am having difficulties now trying to find the last shipment and amount made for the same time period.

I thought I could use something similar to this, but seems I keep getting group by errors if I change anything to work with the date.

Any ideas?
Regards, JAS

[jas2803]

I used amountnumber (an auto generated number + 1 per shipment PK)
Not sure if I did this correctly but it looks like it is giving the right values.....


SELECT monthly.amount, monthly.Comments, clients.client, clients.state, clients.city
FROM clients LEFT OUTER JOIN
(SELECT shipment.client, shipment.amount, shipment.comments, shipment.amountNumber
FROM shipment INNER JOIN
(SELECT MAX(amountNumber) AS max_amountNumber, client
FROM shipment
WHERE (Date BETWEEN '10/31/08' AND '11/30/08')
GROUP BY client) amnt ON shipment.amountNumber = amnt.max_amountNumber) monthly ON
clients.client = monthly.client
WHERE (clients.state = 'AZ')
GROUP BY clients.client, clients.state, clients.city, monthly.amount, monthly.comments
ORDER BY clients.client