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 |
-
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 -
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 -
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