Rank() 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, the duplicated rows are indexed with the same rank, the next index will skip the gap (like placements in a competition).

Syntax

RANK(ORDER BY <column 1>) RANK(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. If there's a conflict, both records gets the same index number, the next record will skip those (like placements in a tournament), meaning there can be gaps between indexes. 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:

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

1. RANK(ORDER BY AddedOn) returns:

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

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

   Rank	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. RANK(PARTITION BY ContactId, Year(AddedOn) ORDER BY CustomerId, AddedOn) returns:

   Rank	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