'How to perform a group by and then do order by on records belonging to same group?

I have a table that has ORDER_LINE_ID as primary key. 2 other columns of concern are STATUS_ID and STATUS_TS. Over the course of time, the STATUS_ID and STATUS_TS change for the same ORDER_LINE_ID and it is all collected up.

Now, I have multiple records with same ORDER_LINE_ID having different STATUS_ID & STATUS_TS. My goal is to "group by" based on ORDER_LINE_ID and then, perform an "Order by" on STATUS_TS, then collect the STATUS_ID, OL_ID and STATUS_TS corresponding to the most recent value of STATUS_TS using a first() function (to select the top record in the group by) or some other means.

Mind that there is a 'HAVING' clause in the code following the "group by" condition to exclude few STATUS_IDs.

Attaching last part of code for reference:

Select

  COLR.ORDER_DT ORDER_DT,
  COLR.ORDER_LINE_ID ORDER_LINE_ID,
  COLR.ORDER_HEADER_KEY ORDER_HEADER_KEY,
  COLR.ORDER_LINE_KEY ORDER_LINE_KEY,
  MIN(CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_ID END)  LINE_STATUS, 
  --Max(CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_QTY END) UNIT,

  SUBSTRING(MIN(CONCAT(LPAD( CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_ID END , 11, '0'), COLR.STATUS_QTY)), 12) AS UNIT,

  SUBSTRING(MAX(CONCAT(LPAD( CASE WHEN COLR.STATUS_QTY > 0 THEN COLR.STATUS_ID END , 11, '0'), COLR.STATUS_DESC)), 12) AS LINE_DESCRIPTION,

  COLR.ITEM_KEY ITEM_KEY,
  COLR.PRODUCT_LINE PRODUCT_LINE,
  COLR.SHIP_NODE_CD SHIP_NODE_CD,
  COLR.RECEIVING_NODE_CD RECEIVING_NODE_CD,
  COLR.LINE_TOTAL_AMT LINE_TOTAL_AMT,                                   --Nile bpk
  COLR.ADDITIONAL_LINE_TYPE_CD ADDITIONAL_LINE_TYPE_CD,                 --Nile bpk
  COLR.RETURN_ACTION_CD RETURN_ACTION_CD,                               --Nile bpk
  COLR.ORDER_QTY ORDER_QTY,                                             --Nile bpk
  COLR.RETURN_REASON_CD RETURN_REASON_CD,                               --Nile bpk
  COLR.RETURN_SUB_REASON_CD RETURN_SUB_REASON_CD,                       --Nile bpk
  COLR.RETURN_REASON_DESC RETURN_REASON_DESC,                           --Nile bpk
  COLR.RETURN_ACTION RETURN_ACTION,                                     --Nile bpk
  MIN(COLR.STATUS_TS) STATUS_TS,                                        --Nile bpk

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700' THEN (COLR.INSERT_TS) ELSE NULL END) ORDER_SHIPPED_DT,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.8000' THEN (COLR.INSERT_TS) ELSE NULL END) STORE_RECEIVED_DT,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.9000' THEN (COLR.INSERT_TS) ELSE NULL END) CUSTOMER_PICKED_UP_DATE,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('9000','3700.01.545','3200.525','9000.300','3200.520','3700.01.540','1100.525') OR (TRIM(COLR.STATUS_ID)) >= '9000'  THEN (COLR.INSERT_TS) ELSE NULL END) CANCELLED_DATE,
  
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.9000' THEN (COLR.INSERT_TS) ELSE NULL END) CUSTOMER_PICKUP_TS,            --Nile bpk
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3200.050' THEN (COLR.INSERT_TS) ELSE NULL END) ORDER_DROP_TS,                  --Nile bpk
  
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('3700.01','3700.01.01','3700.02') THEN (COLR.INSERT_TS) ELSE NULL END) RETURN_DATE,

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('1100','1100.200','1100.525','1300','1310') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) PENDING_OMS_QTY,

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('1500','1500.100','1500.101','3200','3200.050','3200.100','3200.200','3200.500','3200.520') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) PENDING_SHIPMENT_QTY,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('3700','3700.00.03','3700.01.03','3700.01.540','3700.500','3700.7777') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) IN_TRANSIT_QTY,

  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.8000' AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) AWAITING_PICKUP_QTY,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) ='3700.9000' AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) CUSTOMER_PICKED_UP_QTY,
  MIN(CASE WHEN  (TRIM(COLR.STATUS_ID)) in ('1300','1310','1500','1500.100') AND COLR.STATUS_QTY > 0 THEN (COLR.STATUS_QTY) ELSE NULL END) BO_STATUS_QTY --Added for BOSTS story by NILE team

FROM
    (select * from ((select * from TEMP_SALES_ORDER_DATA)UNION(select * from TEMP_RET_ORDER_DATA)) where STATUS_QTY >0
      ) COLR

GROUP BY
 COLR.ORDER_DT,
 COLR.ORDER_LINE_ID,
 COLR.ORDER_LINE_KEY,
 COLR.PRODUCT_LINE,
 COLR.ITEM_KEY,
 COLR.ORDER_HEADER_KEY,
 COLR.SHIP_NODE_CD,
 COLR.RECEIVING_NODE_CD,
 COLR.LINE_TOTAL_AMT,                                         
 COLR.ADDITIONAL_LINE_TYPE_CD,                                
 COLR.RETURN_ACTION_CD,                                       
 COLR.ORDER_QTY,                                              
 COLR.RETURN_REASON_CD,                                       
 COLR.RETURN_SUB_REASON_CD,                                   
 COLR.RETURN_REASON_DESC,                                     
 COLR.RETURN_ACTION                                           


HAVING
LINE_STATUS not in ('3700.01.545','3200.525','9000.300','3200.520','3700.01.540','1100.525')  --Cancelled
AND LINE_STATUS <= '9000' -- Cancelled
AND LINE_STATUS NOT IN ('3700.01.01','3700.02')

I feel that using an order by in a group by could negate the group by operation. Need some guidance here.

sql


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions