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.amo unt) 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]

Might not be perfect but this worked

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.Comment s, clients.client, clients.state, clients.city
FROM clients LEFT OUTER JOIN
(SELECT shipment.client , shipment.amount , shipment.commen ts, shipment.amount Number
FROM shipment INNER JOIN
(SELECT MAX(amountNumbe r) AS max_amountNumbe r, client
FROM shipment
WHERE (Date BETWEEN '10/31/08' AND '11/30/08')
GROUP BY client) amnt ON shipment.amount Number = amnt.max_amount Number) monthly ON
clients.client = monthly.client
WHERE (clients.state = 'AZ')
GROUP BY clients.client, clients.state, clients.city, monthly.amount, monthly.comment s
ORDER BY clients.client