SQL Correlated Subquery - help required with MAX

Question

I have two tables, 'backorders' and 'sku'

sku

id | orderdate | sku | expectedship

backorders

orderdate | ordernum | saleschannel | sku | expectedship

In the sku table, is a list of items that have different expected ship dates based on the date of order.

E.g.

1  | Apple  |  01.08.2014  |  19.10.2014
2  | Apple  |  02.08.2014  |  28.10.2014
3  | Pear   |  02.08.2014  |  20.10.2014
4  | Grape  |  02.08.2014  |  22.10.2014

In the backorders table, is a list of items that have been ordered. The Ordernum is the unique reference where multiple items can be ordered.

E.g.

02.08.2014  |  order1  |  a  |  apple
02.08.2014  |  order1  |  a  |  grape
02.08.2014  |  order1  |  a  |  pear
03.08.2014  |  order2  |  c  |  banana

What I am trying to achieve is for each backorder, populate the max expected ship date across all items for that order.
E.g.

02.08.2014  |  order1  |  a  |  apple  |  28.10.2014
02.08.2014  |  order1  |  a  |  grape  |  28.10.2014
02.08.2014  |  order1  |  a  |  pear   |  28.10.2014
03.08.2014  |  order2  |  c  |  banana |  NULL

As you can see above, is order1 shows the maximum/oldest date for all 3 sku's ordered.

The below query shows me the expected ship date for each sku; however cannot work out how to get the max date per order number.

SELECT
    backorders.orderdate,
    backorders.ordernum,
    backorders.saleschannel,
    backorders.sku,
    setup.expectedship
FROM backorders
LEFT OUTER JOIN setup ON backorders.orderdate = setup.orderdate AND backorders.sku =     setup.sku
WHERE (backorders.saleschannel = 'a') 
   OR (backorders.saleschannel ='b') 
   OR (backorders.saleschannel ='c') 
ORDER BY backorders.ordernum DESC

I can also separately manage to identify the max date for each ordernum:

SELECT
    backorders.ordernum,
    MAX(setup.expectedship) AS `MAX(expectedship)`
FROM backorders
INNER JOIN setup ON backorders.sku = setup.sku AND backorders.orderdate = setup.orderdate
WHERE (setup.orderdate = backorders.orderdate) 
  AND (setup.sku = backorders.sku) 
  AND (backorders.saleschannel = 'a') 
  OR  (backorders.saleschannel ='b') 
  OR  (backorders.saleschannel ='c')
GROUP BY backorders.ordernum

Is there a way I can run the first query to identify the expected ship per order line, and then a subquery to update the expected ship across all ordernum to maximum?

Answer 1

to what I was referring in my comment you could just join the two queries together... i would try to replicate this to ensure it works.. but your date formats are off and I don't have the time to format them all.

SELECT
    t.orderdate,    t.ordernum,
    t.saleschannel, t.sku,
    t1.expectedship
FROM
(   SELECT
        b.orderdate,    b.ordernum,
        b.saleschannel, b.sku,
        s.expectedship
    FROM backorders b
    LEFT OUTER JOIN setup s ON b.orderdate = s.orderdate AND b.sku = s.sku
    WHERE b.saleschannel IN('a', 'b', 'c')
    ORDER BY b.ordernum DESC
)t
LEFT JOIN
(   SELECT
        b.ordernum,
        MAX(s.expectedship) AS expectedship
    FROM backorders b
    INNER JOIN setup s ON b.sku = s.sku AND b.orderdate = s.orderdate
    WHERE (s.sku = b.sku) 
      AND b.saleschannel IN('a', 'b', 'c')
    GROUP BY b.ordernum
)t1 on t1.ordernum = t.ordernum
order by ordernum;

DEMO