Partitioning with groups

Analytics
Fan

Partitioning with groups

Hello All,

I'm very green with sql and I need help. The result i need is to get most recent audit_ID from the selection and eventually grouping by the columns.  I hope that makes sense.

SELECT  DISTINCT max(P.AUDIT_ID) over (partition by P.VENDOR_ID, P.HOST_INVOICE_NUM, P.ITEM_ID,  P.EXT_CHOSEN_GROSS_AMT) as MostRecAud,
P.VENDOR_ID, P.HOST_INVOICE_NUM, P.ITEM_ID,  P.EXT_CHOSEN_GROSS_AMT, P.OA_VOUCHER_NUM, P.RECEIPT_ID,
  P.PO_NUM, P.TO_LOCATION_ID, P.PURCHASE_TYPE_CD, P.INVOICE_SOURCE_TYPE_CD,
  P.REASON_CD, P.AP_COMPANY_CD, P.CURRENCY_CD, P.INVOICE_DT, P.INVOICE_FISCAL_DATE_ID,
  P.INVOICE_HEADER_LAST_UPDATE_DT, P.INVOICE_HEADER_PROCESS_DT, P.RECEIPT_DT,
  P.RECEIPT_ENTRY_DT, P.VENDOR_CALENDAR_IND, P.ITEM_ID, P.PROGRAM_SUBTYPE_CD,
  P.PROGRAM_ELIGIBLE_IND, P.GL_ACCOUNT_ID, P.OA_VOUCHER_LINE_DESC, P.CHARGEBACK_TYPE_CD,
  P.POSTED_PERIOD_ID, P.INVOICE_DETAIL_LAST_UPDATE_DT, P.CONVERSION_RT_TO_USD,
  P.EXT_CHOSEN_GROSS_AMT, P.EXT_CHOSEN_GROSS_USD_AMT, P.REBATABLE_PURCHASE_AMT,
  P.REBATABLE_PURCHASE_USD_AMT, P.REBATABLE_PURCHASE_QTY, P.PROGRAM_AMT,
  P.PROGRAM_USD_AMT, P.EXT_GROSS_AMT, P.CHOSEN_AMT, P.CHOSEN_QTY, P.INVOICE_AMT,
  P.INVOICE_QTY, P.EXT_ADJUSTMENT_AMT, P.ADJUSTMENT_QTY, P.UNINVOICED_AMT,
  P.UNINVOICED_QTY, P.CREATE_TS, P.LAST_UPDATE_TS,
  P.OMX_STORE_ID,P.PRODUCT_CD, P.OMX_VENDOR_ID, P.SAP_DOC_TYPE, P.ARTICLE_OWNERSHIP_FLG,
  P.CHECK_NUM, P.CHECK_DT, P.PURCHASE_SOURCE_CD, P.SAP_DOC_VI_TXT
FROM REPORTING_V.RPT_PURCHASES P
LEFT JOIN GDW_VIEWS.VENDOR V ON P.VENDOR_ID = V.VENDOR_ID  
WHERE P.PURCHASE_SOURCE_CD = 'ODP'   
AND P.INVOICE_DT between  '2017-12-31' AND '2018-12-29'
AND V.Vendor_num = '397'  
and p.host_invoice_num = '1708824'  
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26, 27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44, 45,46,47,48,49,50,51,52,53,54,55,56

3 REPLIES 3
Highlighted
Junior Contributor

Re: Partitioning with groups

It's hard to tell what you want, you use both DISTINCT and GROUP BY and try to add a MAX OVER on top of it?

 

Btw, you wrote LEFT join, but you get result of an INNER Join due to the AND V.Vendor_num = '397' condition

Fan

Re: Partitioning with groups

There are duplicates in the fields where there are partitions but the only result I want is the max(audit_id). 

Junior Contributor

Re: Partitioning with groups

I still don't get what you want, only the row(s) with the max(audit_id)?

Then you need to add something like

QUALIFY
   P.AUDIT_ID = max(P.AUDIT_ID) over (partition by P.VENDOR_ID, P.HOST_INVOICE_NUM, P.ITEM_ID,  P.EXT_CHOSEN_GROSS_AMT)

or

QUALIFY -- if there are multiple rows per AUDIT_ID use RANK instead of ROW_NUMBER
   ROW_NUMBER() over (partition by P.VENDOR_ID, P.HOST_INVOICE_NUM, P.ITEM_ID,  P.EXT_CHOSEN_GROSS_AMT
                      ORDER BY P.AUDIT_ID DESC) = 1

instead of DISTINCT/GROUP BY