First-Last
    • Dark
      Light

    First-Last

    • Dark
      Light

    Article Summary

    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.

    Snowflake Window functions.

    AWS Window functions.

    BigQuery Window functions.

    Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.
    Grouping ColumnsList of ColumnsDefines 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 partitionsInput ColumnThe input column name for sorting within the grouped data. You can drag to reorder.
    OrderingThe order of the sorting: Ascending (Asc) or Descending (Desc).
    First/Last ColumnsColumnThe name of the input column to be passed to the output.
    First/LastReturn the First or Last element of the chosen column in the grouped data.
    Ignore NullsSelectSelect 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.

    Snowflake First Value.

    Snowflake Last Value.

    Redshift First Value and Last Value.

    BigQuery First Value.

    BigQuery 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.