Skip to main content
The First/Last transformation component lets you create groups of data, sort these groups, and then return only the first or last row of each group. This component is equivalent to writing a SELECT statement with a window function in-line using the OVER keyword. For more information, read the following documentation:
SnowflakeDatabricksAmazon RedshiftGoogle BigQuery
Snowflake Window functionsDatabricks Window functionsAmazon Redshift Window functionsBigQuery Analytic (window) functions
Snowflake First ValueDatabricks Window function First ValueAmazon Redshift Window function First ValueBigQuery FIRST_VALUE function
Snowflake Last ValueDatabricks Window function Last ValueAmazon Redshift Window function Last ValueBigQuery LAST_VALUE function

​
Use case

This component can be used to simplify workflows that rely on identifying the start or end points of a time period or sequence of events. For example, you can use it to:
  • Group purchase data by customer, sort by purchase date, then use First to find the details of each customer’s first purchase.
  • Group user logins by user, sort by most recent timestamp, then use Last to see when your users last logged in.
  • Group monthly inventory data by time period, then use two First/Last components to view your inventory levels at the start and end of each month.

​
Properties

​
Name
string
required
A human-readable name for the component.
​
Grouping columns
dual listbox
required
Defines how the input data is grouped. This works like an SQL GROUP BY statement. The first or last element of each group will be selected.To use grid variables, select the Use Grid Variable checkbox at the top of the dialog. For more information, read Grid variables.
​
Ordering in partitions
column editor
required
  • Input Column: The name of the column to use for sorting within the grouped data.
  • Ordering: Specify how the selected column is sorted. Options are Ascending, Descending, Nulls First, or Nulls Last. For Google BigQuery, only Ascending and Descending are available.
Click the Text mode toggle at the bottom of the dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.To use grid variables, toggle Use Grid Variable on at the bottom of the dialog. For more information, read Grid variables.
​
First last columns
column editor
required
  • Column: The name of the input column to be passed to the output.
  • First Last: Return the First or Last element of the chosen column in the grouped data.
Click the Add all button to automatically populate the Column list with all the columns which were not selected in the Grouping columns property. First Last is set to First in each row. You can then selectively edit the list if required, but if you click Add all again it will return the list to the default fully populated state, removing any changes you have made.Click the Text mode toggle at the bottom of the dialog to open a multi-line editor that lets you add items in a single block. For more information, read Text mode.To use grid variables, toggle Use Grid Variable on at the bottom of the dialog. For more information, read Grid variables.
​
Ignore nulls
boolean
required
Select whether to ignore null values. The default setting is No (do not ignore null values).