.png){kind=logo}
First-Last
-
DarkLight
First-Last
-
DarkLight
This article is specific to the following platforms - Snowflake - Redshift - BigQuery - Synapse.
First/Last Component
The First/Last component allows users to create groups of data and then return only the first or last rows from the group.
Properties
| Property | Setting | Description |
|---|---|---|
| Name | Text | A human-readable name for the component. |
| Grouping Columns | List of Columns | 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 | Input Column | The input column name for sorting within the grouped data. You can drag to reorder. |
| Ordering | The order of the sorting: Ascending (Asc) or Descending (Desc). | |
| First/Last Columns | 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. | |
| Ignore Nulls | Select | Select whether to ignore null values. The default setting is No. |
Strategy
Generates a select statement with a window function in line using the OVER keyword.
Redshift First Value and Last Value.
Example
This example returns the first flight for a given plane in a given year. The filter removes some null data rows.
The component properties are set up as below. We group by plane (tailnum) and then year to get the correct aggregation, and then sort by the date for each plane (tailnum, year, month, dayofmonth).
For each column not involved in the grouping we must specify First or Last if we want to see that column in the output.
The output data shows the date of the first flight for each plane.
