Skip to main content

Celonis Product Documentation

LAST
Description

Returns the last element of the specified source column for each element in a group.

LAST can be applied to any data type. The data type of the result is the same as the input column data type.

Syntax
 LAST ( table.input_column [, ORDER BY table.column [ASC|DESC] ] )

  • table.input_column: The column which should be aggregated.

  • ORDER BY (optional): Elements of the specified column are used to determine the last element. ASC or DESC can be specified to use ascending or descending ordering. If the order direction is not specified, the ascending (ASC) order is used. Using LAST with descending order is equivalent to using FIRST with ascending order.

Warning

Ordering without explicit ORDER BY There is no guarantee on the order of the result returned if no explicit ORDER BY column is given. The only clearly defined cases without ORDER BY are when:

  1. using it on a table with implicit sorting (e.g., an activity table).

  2. using to access a column of a grouper key (e.g. if your grouping by column A and B and access the value of column A in an aggregation column) or a value depends on the grouper key.

NULL handling

NULL values are ignored, so they do not influence the result. If all the values of a group are NULL, the result for this group is also NULL.

Examples

[1]

This example computes the last element of a column.

Query

Column1 

         LAST ( "Table"."Value" )

Input

Output

Table

Value : int

1

2

3

Result

Column1 : int

3

[2]

This example computes the last value grouped by country.

Query

Column1 

         "Table"."Country"

Column2 

         LAST ( "Table"."Value" )

Input

Output

Table

Country : string

Value : int

'US'

3

'DE'

10

'DE'

5

'FR'

5

'US'

4

'US'

1

Result

Column1 : string

Column2 : int

'DE'

5

'FR'

5

'US'

1

[3]

This example demonstrates the use of the optional ORDER BY clause.

Query

Column1 

         "caseTable"."companyCode"

Column2 

         LAST ( "caseTable"."caseId" , ORDER BY "caseTable"."value" )

Input

Output

caseTable

caseId : int

companyCode : string

value : int

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

100

5

'002'

500

6

'003'

200

Result

Column1 : string

Column2 : int

'001'

1

'002'

5

'003'

6

[4]

This example computes the last element in a group with null values.

Query

Column1 

         "Table"."Country"

Column2 

         LAST ( "Table"."Value" )

Input

Output

Table

Country : string

Value : int

'US'

null

'DE'

null

'DE'

null

'FR'

5

'US'

4

'US'

1

Result

Column1 : string

Column2 : int

'DE'

null

'FR'

5

'US'

1

See also: