CODEWITHSUNDEEP
sqloracle-databasemaxaggregate-functions
artemis
artemis

Reputation: 7261

Finding MAX date aggregated by order - Oracle SQL

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      |

Upvotes: 2

Views: 493

Answers (3)

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

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.)

Upvotes: 0

MT0
MT0

Reputation: 167982

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) |

Upvotes: 3

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 4

Related Questions