Help with Row_Number(), Partition By, Order By

Question

I have multiple partition by in TSQL and need help to replicate the same in Alteryx

Scenario 1:

RowNumber ( )

Over

(

Partition by       Ln_Id

Order by           Acg_Prd_Id    DESC

) As RowNum

Scenario 2:

RowNumber ( )

Over

(

Partition by       Ln_Id

Order by           Acg_Prd_Id       DESC,

Ln_Bus_Evnt    DESC

) As RowNum

Scenario 2:

RowNumber ( )

Over (

Partition by                  Ln_Id,

Ln_Actg_Bal_Typ

Order by                      Acg_Prd_Id   DESC,

Ln_Bus_Evnt DESC,

Ln_Actg_Acvy_Id DESC

) As RowNum

Scenario 4:

RowNumber ( )

Over

(

Partition by                  Ln_Id,

Ln_Actg_Bal_Typ

Order by                      Acg_Prd_Id DESC

) As RowNum

Thank you very much for spending your valuable time for me.

danilang · Answer

Hi @Reddy4

The general methodology to reproduce the Row_number over (Partition Order By) TSQL is to sort by all the fields in both the Partition By and Order By clauses in the order that they appear in the SQL statement and then use a multi-row tool grouped by the fields in the Partition By clause

Make sure to specify the direction in the sort tool for the Order By fields

The attached workflow contains an example for each of the 4 scenarios you included.

Dan

RowNumberOverPartition.yxmd

Reddy4 · Answer

I cannot use In-DB tools due to gallery connections. Can I know how it can be achieved using Alteryx tools?

Thank you

TheOC · Answer

hey @Reddy4

If you are using the Alteryx in-db connections, i believe you can achieve this via an in-db summarize tool:

https://help.alteryx.com/20213/designer/summarize-db-tool

This will allow you to group by, and select counts.

Hope this helps,
TheOC