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 Redshift
Snowflake Window functionsDatabricks Window functionsAmazon Redshift Window functions
Snowflake First ValueDatabricks Window function First ValueAmazon Redshift Window function First Value
Snowflake Last ValueDatabricks Window function Last ValueAmazon Redshift Window function Last Value

​
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.
​
Ordering within partitions
column editor
required
Input Column: The name of the column to use for sorting within the grouped data.Ordering: The method of sorting on the chosen column. Options are Ascending, Descending, Nulls First, or Nulls Last.
​
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.
​
Ignore Nulls
boolean
required
Select whether to ignore null values. The default setting is No (do not ignore null values).