RowNumber() mvSQL database function in Jitterbit App Builder

Summary

Sequentially numbers each row within a set, ordered by one or more columns, starting at 1. This function requires the ORDER BY modifier and also supports the PARTITION BY modifier. If the ORDER BY is not unique within the set, each conflicting row is indexed sequentially in a nondeterministic way and may change between executions.

Syntax

ROWNUMBER(ORDER BY <column 1>) ROWNUMBER(PARTITION BY <column 2> ORDER BY <column 1>)

Parameters

modifier

This function requires the ORDER BY modifier and supports the PARTITION BY modifier.

column 1

A reference to a column.

column 2

A reference to a column.

Returns

A sequential number assigned to each row in the query result set. The ORDER BY modifier is required, and the PARTITION BY modifier is optional and also supported. Depending on the modifiers used in the syntax the results will be sorted accordingly.

Examples

For the examples provided below, we will assume the following data is available to query:

1. ROWNUMBER(ORDER BY AddedOn) returns:

   RowNumber	CustomerId	ContactId	AddedOn
   1	1	1	2023-04-01
   3	1	2	2023-04-02
   2	2	3	2023-04-01
   4	2	3	2023-04-03

2. ROWNUMBER(PARTITION BY CustomerId ORDER BY AddedOn) returns:

   RowNumber	CustomerId	ContactId	AddedOn
   1	1	1	2023-04-01
   2	1	2	2023-04-02
   1	2	3	2023-04-01
   2	2	3	2023-04-03

3. ROWNUMBER(PARTITION BY ContactId, Year(AddedOn) ORDER BY CustomerId, AddedOn) returns:

   RowNumber	CustomerId	ContactId	AddedOn
   1	1	1	2023-04-01
   1	1	2	2023-04-02
   1	2	3	2023-04-01
   2	2	3	2023-04-03