Hello all,

I have a little problem that i seem to be at a loss at solving efficiently. Here's the situation

table : order_status_tracking
cols : id (int), orderId (int), newStatus(int), date(date), time(time)

each time an order gets a new status there is a row inserted in this table. now i have to generate a table that would get the last inserted status between a given date for an order. i am getting stuck at getting the correct status from the data.. i get the correct id, orderId, date but not the status.. it shows me the first status in the rows selected based on the date criteria.


SELECT MAX(ost.id) as ostId, MAX(ost.date), ost.newStatus, o.serviceType FROM order_status_tracking ost INNSER JOIN orders o ON (o.id = ost.orderId) WHERE date >= $sDate AND date < $eDate GROUP BY ost.orderId



i can't do a MAX on o.newStatus as the max id is not always the last status selected. what happens is that if there are multiple rows for an orderId, it selects the *last* id, *last date*, but the *first newStatus* from that last

any ideas how to get around this ? thanks!

nevermind



SELECT ost.id, ost.orderId, ost.newStatus
FROM order_status_tracking AS ost
INNER JOIN order_status_tracking AS ost2 ON ost.orderId = ost2.orderId
WHERE ost.date >= '2004-12-29' AND ost.date <= '2004-12-29'
GROUP BY ost.id, ost.orderId, ost.newStatus
HAVING ost.id = MAX( ost2.id )
ORDER BY `ost`.`id`