Finding MAX date aggregated by order - Oracle SQL

Question

I have a data orders that looks like this:

| Order | Step | Step Complete Date |
|:-----:|:----:|:------------------:|
|   A   |   1  |      11/1/2019     |
|       |   2  |      11/1/2019     |
|       |   3  |      11/1/2019     |
|       |   4  |      11/3/2019     |
|       |   5  |      11/3/2019     |
|       |   6  |      11/5/2019     |
|       |   7  |      11/5/2019     |
|   B   |   1  |      12/1/2019     |
|       |   2  |      12/2/2019     |
|       |   3  |                    |
|   C   |   1  |     10/21/2019     |
|       |   2  |     10/23/2019     |
|       |   3  |     10/25/2019     |
|       |   4  |     10/25/2019     |
|       |   5  |     10/25/2019     |
|       |   6  |                    |
|       |   7  |     10/27/2019     |
|       |   8  |     10/28/2019     |
|       |   9  |     10/29/2019     |
|       |  10  |     10/30/2019     |
|   D   |   1  |     10/30/2019     |
|       |   2  |      11/1/2019     |
|       |   3  |      11/1/2019     |
|       |   4  |      11/2/2019     |
|       |   5  |      11/2/2019     |

What I need to accomplish is the following: For each order, assign the 'Order_Completion_Date' field as the most recent 'Step_Complete_Date'. If ANY 'Step_Complete_Date' is NULL, then the value for 'Order_Completion_Date' should be NULL.

I set up a SQL FIDDLE with this data and my attempt, below:

SELECT
  OrderNum,
  MAX(Step_Complete_Date)

FROM
  OrderNums

WHERE
  Step_Complete_Date IS NOT NULL

GROUP BY
  OrderNum

This is yielding:

ORDERNUM    MAX(STEP_COMPLETE_DATE)
D   11/2/2019
A   11/5/2019
B   12/2/2019
C   10/30/2019

How can I achieve:

| OrderNum | Order_Completed_Date |
|:--------:|:--------------------:|
|     A    |       11/5/2019      |
|     B    |         NULL         |
|     C    |         NULL         |
|     D    |       11/2/2019      |

Why are you storing date values as strings? You should use an appropriate data type; so for dates use a `DATE` data type. — MT0, Dec 04 '19 at 21:44
Not intentional -- I copied the data over from MS Excel in SQL FIDDLE, so I think that was just an assumption made on their end. I should have double checked that. @MT0 — artemis, Dec 04 '19 at 22:12

score 4 · Answer 1 · answered Dec 04 '19 at 21:37

4

Aggregate function with KEEP can handle this

select ordernum, 
       max(step_complete_date) 
          keep (DENSE_RANK FIRST ORDER BY step_complete_date desc nulls first) res
FROM
  OrderNums
GROUP BY
  OrderNum

answered Dec 04 '19 at 21:37

Gary Myers

34,963
3
49
74

this is interesting, I've never heard of `KEEP` before. I looked some information up on it. Can you add to your post a bit more in depth what this function is doing? – artemis Dec 04 '19 at 22:14
1

@JerryM. As described in [this answer](https://stackoverflow.com/a/20157109/1509264), read the expression from right-to-left. In each group, first `ORDER BY step_complete_date` in `DESC`ending order with the `NULLS FIRST` and give those rows a `DENSE_RANK` (so `NULL` values are ranked first then the maximum value next and so on to the lowest) and then `KEEP` the highest ranked values for each group and finally, from those kept rows, return the `MAX(step_complete_date)`. – MT0 Dec 04 '19 at 22:48

score 3 · Accepted Answer · answered Dec 04 '19 at 21:37

You can use a CASE expression to first count if there are any NULL values and if not then find the maximum value:

Query 1:

SELECT OrderNum,
       CASE
       WHEN COUNT( CASE WHEN Step_Complete_Date IS NULL THEN 1 END ) > 0
       THEN NULL
       ELSE MAX(Step_Complete_Date)
       END AS Order_Completion_Date
FROM   OrderNums
GROUP BY OrderNum

Results:

| ORDERNUM | ORDER_COMPLETION_DATE |
|----------|-----------------------|
|        D |             11/2/2019 |
|        A |             11/5/2019 |
|        B |                (null) |
|        C |                (null) |

score 0 · Answer 3 · answered Dec 04 '19 at 21:41

First, you are representing dates as varchars in mm/dd/yyyy format (at least in fiddle). With max function it can produce incorrect result, try for example order with dates '11/10/2019' and '11/2/2019'.

Second, the most simple solution is IMHO to use fallback date for nulls and get null back when fallback date wins:

SELECT
  OrderNum,
  NULLIF(MAX(NVL(Step_Complete_Date,'~')),'~')
FROM
  OrderNums
GROUP BY
  OrderNum

(Example is still for varchars since tilde is greater than any digit. For dates, you could use 9999-12-31, for instance.)

Finding MAX date aggregated by order - Oracle SQL

3 Answers3