How to retrieve records with max `delivery date` for each `order number`

Question

I am looking to retrieve all the the records and columns by order number of a table TEST where for a each order number the MAX of the column orderDeliveryDate is equal to yesterday. The following code does not work:

SELECT  T.* 
FROM    TEST T
    INNER JOIN(
            SELECT orderNum, MAX(orderDeliveryDate) AS maxDeliveryDate
            FROM TEST
            GROUP BY orderNum) MX
        ON T.orderNum= MX.orderNum
        AND T.orderDeliveryDate = MX.maxDeliveryDate
        AND MX.maxDeliveryDate = DATE_SUB(curdate(),INTERVAL 1 DAY)

John Kuhns · Answer 1 · 2015-09-11T20:44:25.197

0

This should do it:

SELECT * FROM TEST 
WHERE ORDERNUM IN 
    (SELECT ORDERNUM, MAX(orderDeliveryDate)
     FROM TEST GROUP BY ORDERNUM 
     HAVING DATE(MAX(orderDeliveryDate)) = DATE(DATE_SUB(curdate(),INTERVAL 1 DAY)))

The guess I'm making is orderDeliveryDate is a DateTime, so converting it to a Date will fix your problem. Your original query would work with the conversion to Date, I think mine is just a little more straightforward.

edited Sep 11 '15 at 20:44

answered Sep 11 '15 at 20:33

John Kuhns

506
4
20

2

You need to at least group by orderNum – Bohemian Sep 11 '15 at 20:41
If it's a date, your query should work. If it has a time in it, it won't. In other words 2015-09-11 08.00.00.000 is not equal to 2015-09-11 09.00.00.000 – John Kuhns Sep 11 '15 at 20:45
Yes it is a date. The query does not work: MySQL returned an empty result set (i.e. zero rows). (Query took 0.3480 sec) – Arnaud Martinn Sep 11 '15 at 21:05

score 0 · Answer 2 · answered Sep 11 '15 at 21:39

0

Convert the column to a date before comparing, and for an efficiency move the test into the sub query:

SELECT  T.* 
FROM TEST T
JOIN(
    SELECT orderNum, DATE(MAX(orderDeliveryDate)) AS maxDeliveryDate
    FROM TEST
    GROUP BY orderNum
    HAVING DATE(MAX(orderDeliveryDate)) = DATE_SUB(curdate(),INTERVAL 1 DAY)
    ) MX
    ON T.orderNum= MX.orderNum
    AND DATE(T.orderDeliveryDate) = MX.maxDeliveryDate

answered Sep 11 '15 at 21:39

Bohemian

412,405
93
575
722

Hi Bohemian - it does not yield any result. I notice you've switch to JOIN instead of INNER JOIN. – Arnaud Martinn Sep 12 '15 at 07:31
@ArnaudMartinn JOIN *is* INNER JOIN. They are identical - "INNER" is optional and has no effect when coded – Bohemian Sep 12 '15 at 09:24
the code does not yield the expected result: MySQL returned an empty result set (i.e. zero rows). (Query took 0.1572 sec) – Arnaud Martinn Sep 13 '15 at 00:11
Is there data matching yesterday (considering it is currently the weekend)? – Bohemian Sep 13 '15 at 09:27

How to retrieve records with max `delivery date` for each `order number`

2 Answers2