CODEWITHSUNDEEP
sqloracle-database
a girl has no name
a girl has no name

Reputation: 57

Oracle SQL Query Get Value at Max Date

Data:

| ORIGL_ORDER | ORDER | ITEM | DATE        | WHSE | QTY |
| ----------- | ----- | ---- | ----------  | ---- | --- |
| 001         | 107   | 9    | 12/29/2020  | 7    | 6   |
| 001         | 110   | 9    | 12/30/2020  | 1    | 4   |
| 001         | 113   | 9    | 12/30/2020  | 3    | 2   |
| 007         | 211   | 3    | 12/20/2020  | 6    | 1   |
| 007         | 219   | 3    | 12/19/2020  | 5    | 3   | 
| 018         | 390   | 8    | 12/25/2020  | 2    | 1   |

Original Desired Result:

| ORIGL_ORDER | ORDER | ITEM | MAX_DATE    | WHSE |
| ----------- | ----- | ---- | ----------  | ---- |
| 001         | 113   | 9    | 12/30/2020  | 3    |
| 007         | 211   | 3    | 12/20/2020  | 6    |
| 018         | 390   | 8    | 12/25/2020  | 2    |

Original Question: For each ORIGL_ORDER and ITEM, I would like to get the max(DATE) and the WHSE corresponding to the max(Date). If there are more than one orders with the same max(DATE) then the select the largest ORDER number.

New Desired Result:

| ORIGL_ORDER | ORDER | ITEM | MAX_DATE    | WHSE | TOTAL_QTY |
| ----------- | ----- | ---- | ----------  | ---- | --------- |
| 001         | 113   | 9    | 12/30/2020  | 3    | 12        |
| 007         | 211   | 3    | 12/20/2020  | 6    | 4         |
| 018         | 390   | 8    | 12/25/2020  | 2    | 1         |

Upvotes: 1

Views: 198

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

This answers the original version of the question.

Just use row_number():

select t.*
from (select t.*,
             row_number() over (partition by ORIGL_ORDER, item order by date desc, order_number desc) as seqnum
      from t
     ) t
where seqnum = 1;

Upvotes: 3

Related Questions