DenseRank() 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 continue consecutively, without gaps.

Syntax

DENSERANK(ORDER BY <column 1>) DENSERANK(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. There is no gap between indexes with this function. 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. DENSERANK(ORDER BY AddedOn) returns:

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

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

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

   DenseRank	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