Results 1 to 2 of 2

Thread: Mysql SELECT

  1. #1
    Join Date
    Dec 2004

    Unanswered: Mysql SELECT

    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( as ostId, MAX(, ost.newStatus, o.serviceType FROM order_status_tracking ost INNSER JOIN orders o ON ( = 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!

  2. #2
    Join Date
    Dec 2004

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts