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 · Accepted solution]

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

[mohdfaraz47]

Thanks a lot rarmstrong. Appreciate the quick help here. Always a fan of Alteryx community. Never thought it to be this

---

@rarmstrong wrote:

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

---

simple.