Rownum over partition by

mohdfaraz47

select  serial_num, 
ARTICLE,
posting_dt,
mvmnt_type,
doc_num,
VALUATION_TYPE,
DISPOSITION_CD,
PROGRAM_IND,
row_number() over( partition by serial_num order by posting_dt asc,doc_num asc) as ROWNUM
FROM   scm_prd_qmvm.WSC_BI_FACT_SAP_MOVEMENTS_V
WHERE TRIM(LEADING '0' FROM SERIAL_NUM)  IN ( Subquery follows here)

this will give me the result like this:

SERIAL_NUM	POS_LOC_CD	ARTICLE	POSTING_DT	MVMNT_TYPE	DOC_NUM	VALUATION_TYPE	DISPOSITION_CD	PROGRAM_IND	rownum
123	4000050	XX	4/2/2018	653	14	XX	XX	XX	1
123	4000050	XX	4/2/2018	309	13	XX	XX	XX	2
123	4000050	XX	4/2/2018	309	13	XX	XX	XX	3
123	4000050	XX	4/4/2018	309	8	XX	XX	XX	4
123	4000050	XX	4/4/2018	309	8	XX	XX	XX	5
123	4000050	XX	4/4/2018	311	4	XX	XX	XX	6
123	4000050	XX	4/4/2018	311	4	XX	XX	XX	7

i need this type of output in Alteryx using in db tools where i can partition the data in Serial number and can use the row num to pick up any value depending up on that. Mostly i use row num =1 and it all depends on the posting dt and doc num either being sorted by asc or desc. 

Any suggestions on this is highly appreciated 

rarmstrong

You can do this with a combination or Sort tool, Formula tool and Multi-Row Tool. The workflow attached is an example.

sample.yxmd