0

I have a table called TEST with 5 columns: clientName, clientID, productNum, orderNum, orderDeliveryDate, and thousands of rows. It basically stores orders. Each order can be composed of several product items, and each product item can have a specific delivery date.

I need to get a table which shows, for each orderNum the latest orderDeliveryDate as well the rest of the TEST table columns (nb: NULL values must be excluded because it should not be considered as valid dates).

I know I need to use JOIN, but can't find the solution.

SELECT * 
FROM `TEST`
INNER JOIN
    (SELECT 
         orderNum, MAX(orderDeliveryDate) AS maxdate
     FROM TEST
     GROUP BY orderNum) groupedorders ON TEST.orderNum = groupedorders.orderNum
                                      AND TEST.orderDeliveryDate = groupedorders.maxdate
GROUP BY orderNum
ORDER BY groupedorders.maxdate ASC

Can anyone help me solve this?

Thank you for your help.

Horia
  • 1,612
  • 11
  • 18
Arnaud Martinn
  • 95
  • 2
  • 11
  • Please read [How to ask a **good** question?](http://stackoverflow.com/help/how-to-ask) and lean from it – marc_s Aug 16 '15 at 18:44
  • Thank you marc_s. I have edited the question for better clarity. – Arnaud Martinn Aug 16 '15 at 20:40
  • Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Aug 17 '15 at 04:16

1 Answers1

1

You dont need to JOIN, unless you want to hook in another table. All you need to do is take the MAX value. NULL values are usually excluded from groupings, but if needed, just add WHERE orderDeliveryDate IS NOT NULL

SELECT orderNum, MAX(orderDeliveryDate) maxDeliveryDate
FROM    test
GROUP BY orderNum

If however, you need to show more data, not just the orderNum and latest delivery date, please detail.

EDIT: This brings back latest delivery date until yesterday

SELECT  T.* 
FROM    TEST T
        INNER JOIN(
                SELECT orderNum, MAX(orderDeliveryDate) AS maxDeliveryDate
                FROM TEST
                WHERE orderDeliveryDate <= (yesterday)
                GROUP BY orderNum) MX
            ON T.orderNum= MX.orderNum
            AND T.orderDeliveryDate = MX.maxDeliveryDate

EDIT2: This following query brings back all records that have latest delivery date = yesterday

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)
Horia
  • 1,612
  • 11
  • 18
  • Hi Horia. Thank you for this answer. Yes I need to see all of the other columns of my table (which contains more rows than I have described here for simplication sake). – Arnaud Martinn Aug 16 '15 at 20:53
  • ok, have a look at 2nd query - i think you were there, but you didn't need to add any grouping except in subquery – Horia Aug 16 '15 at 20:59
  • Thank you Horia. And what if from there we would like to see only the result where maxDeliveryDate = yesterday? Where should we add the: `=DATE_SUB(curdate(),INTERVAL 1 DAY)` statement ?. – Arnaud Martinn Aug 16 '15 at 21:36
  • see the 2nd example again, i`ve added a where clause there - basically you get the latest delivery date up to the date you want. – Horia Aug 16 '15 at 21:53
  • Horia, this solution: displays results where the `orderDeliveryDate` is `yesterday` BUT also for which yesterday is not the latest date. The solution I am looking for must only display rows for which the latest `orderDeliveryDate` (for a given `orderNum`) is equal to `yesterday`. – Arnaud Martinn Aug 17 '15 at 04:01
  • Ok, see EDIT2. Just a tip for you, it's good to actually post the question from start with everything you need. – Horia Aug 17 '15 at 06:12
  • Hey Horia - Sorry to reopen the topic but after some testing I realize your answer does not retrieve return an empty results (when it shouldn't) -- Can you review it please? – Arnaud Martinn Sep 11 '15 at 20:00