Mysql how to reduce rows by max datetime and group by non-uniqe id -

i having severe case of brain flatulence.

keeping simple, have 3 tables: orders, statuses, xrefordersstatuses

i've setup sqlfiddle (sqlfiddle here) simplified schema , redacted row data.

what need orders recent xrefordersstatuses (joined statuses). can sort of using following query:

select o.shopper_name_first, o.shopper_name_last, os.os_name, x.xos_order_id, x.xos_status_id, x.xos_datetime orders o  inner join xrefordersstatuses x on x.xos_order_id = o.order_id left join statuses os on os.os_id = x.xos_status_id order x.xos_order_id, x.xos_datetime desc 

the result looks like:

| shopper_name_first | shopper_name_last |             os_name | xos_order_id | xos_status_id |           xos_datetime | |--------------------|-------------------|---------------------|--------------|---------------|------------------------| |              sally |               sue | order pre-processed |        34049 |            31 | june, 18 2015 12:42:50 | |              sally |               sue |          order paid |        34049 |            20 | june, 18 2015 12:36:30 | |              sally |               sue |       order created |        34049 |            10 | june, 18 2015 12:34:56 | |                joe |            schmoe | order pre-processed |        34050 |            31 | june, 18 2015 12:54:50 | |                joe |            schmoe |          order paid |        34050 |            20 | june, 18 2015 12:38:30 | |                joe |            schmoe |       order created |        34050 |            10 | june, 18 2015 12:34:56 | |              peter |             piper |          order paid |        34051 |            20 | june, 18 2015 12:37:30 | |              peter |             piper |       order created |        34051 |            10 | june, 18 2015 12:34:56 | 

in production scenario select(ing) more/all of orders table columns, left them out here simplicity. note duplicate orders, statuses line up.

in view i'm working on purpose not find recent statuses. view lists orders, , want join/match each order respective recent status based on maximum datetime column value. one-to-many schema (one order many statuses).

so want is:

• select order
• join xref max(xos_datetime) group xos_order_id
• join statuses definitions

orders must remain left side table in query (for other joins used other tables). after going through many other questions , answers , many google searches have not quite found need.

if needed xrefordersstatuses max datetime , grouped order id, query....

select x.* xrefordersstatuses x join (   select xos_order_id, max(xos_datetime) maxdate   xrefordersstatuses   group xos_order_id   ) x1 on x1.xos_order_id = x.xos_order_id , x1.maxdate = x.xos_datetime; 

resulting in:

| xos_id | xos_order_id | xos_status_id |           xos_datetime | |--------|--------------|---------------|------------------------| | 118287 |        34051 |            20 | june, 18 2015 12:37:30 | | 118289 |        34049 |            31 | june, 18 2015 12:42:50 | | 118290 |        34050 |            31 | june, 18 2015 12:54:50 | 

... yay! recent status each order!

alas, need orders table left side table (other joins , searches -- i.e. name, active/inactive, assigned user, etc).

my trouble combining orders query xrefordersstatuses query. cannot quite seem them play together. attempts fail ordering, , grouping, , sorts of heated messes.

i feel gets me close, getting grouping , ordering correct i'm flatulating:

select o.shopper_name_first, o.shopper_name_last, os.os_name, x.xos_order_id, x.xos_status_id, x.xos_datetime orders o  join (   select xz.*   xrefordersstatuses xz   join (     select xos_order_id, max(xos_datetime) maxdate     xrefordersstatuses     group xos_order_id   ) x1 on x1.xos_order_id = xz.xos_order_id , x1.maxdate = xz.xos_datetime ) x left join statuses os on os.os_id = x.xos_status_id order x.xos_order_id, x.xos_datetime desc; 

result looks this:

| shopper_name_first | shopper_name_last |             os_name | xos_order_id | xos_status_id |           xos_datetime | |--------------------|-------------------|---------------------|--------------|---------------|------------------------| |                joe |            schmoe | order pre-processed |        34049 |            31 | june, 18 2015 12:42:50 | |              peter |             piper | order pre-processed |        34049 |            31 | june, 18 2015 12:42:50 | |              sally |               sue | order pre-processed |        34049 |            31 | june, 18 2015 12:42:50 | |                joe |            schmoe | order pre-processed |        34050 |            31 | june, 18 2015 12:54:50 | |              peter |             piper | order pre-processed |        34050 |            31 | june, 18 2015 12:54:50 | |              sally |               sue | order pre-processed |        34050 |            31 | june, 18 2015 12:54:50 | |              peter |             piper |          order paid |        34051 |            20 | june, 18 2015 12:37:30 | |              sally |               sue |          order paid |        34051 |            20 | june, 18 2015 12:37:30 | |                joe |            schmoe |          order paid |        34051 |            20 | june, 18 2015 12:37:30 | 

i tried different variations, tried working forward, backward, left, right, in, out, up, down, hot, cold, wet, dry... picture.

what need this:

| shopper_name_first | shopper_name_last |             os_name | xos_order_id | xos_status_id |           xos_datetime | |--------------------|-------------------|---------------------|--------------|---------------|------------------------| |              sally |               sue | order pre-processed |        34049 |            31 | june, 18 2015 12:42:50 | |                joe |            schmoe | order pre-processed |        34050 |            31 | june, 18 2015 12:54:50 | |              peter |             piper |          order paid |        34051 |            20 | june, 18 2015 12:37:30 | 

if go first query ... need results, reduced recent status each order remains. i'm doing after fact in php ... hence revisiting query remove seemingly unnecessary step php.

perhaps solution xrefordersstatuses query (second 1 above) use right join grab order , status tables?

thoughts anyone? sorry making long (almost tl;dr myself), i'm hoping i've annotated question appropriately.

btw -- i'm longtime lurker of (innumerable questions , tips have been gleaned questions found on here!) first time i've gotten stuck , been unable figure out how need.

---

edit/answer: got query setup way need, , discovered performance problems column , indexing related. kind of going hospital abdominal pain, told have crohns, discover have tumor on kidney -- , things turn out in end.

# query answer user linoff mods select o.shopper_name_first, o.shopper_name_last, s.os_name, x.* orders o  right join xrefordersstatuses x on x.xos_order_id = o.order_id  right join (   select xos_order_id, max(xos_datetime) maxdate   xrefordersstatuses   group xos_order_id ) xmax on xmax.xos_order_id = x.xos_order_id , xmax.maxdate = x.xos_datetime left join statuses s on s.os_id = x.xos_status_id order o.order_datetime desc; 

is looking for?

select <choose columns here> orders o left join      xrefordersstatuses x      on x.xos_order_id = o.order_id left join      (select xos_order_id, max(xos_datetime) maxdate       xrefordersstatuses       group xos_order_id      ) xmax      on xmax.xos_order_id = x.xos_order_id ,         xmax.maxdate = x.xos_datetime; 

the left join necessary if have orders no status records.