Allows the user to create groups of data and then return only the first or last rows from the group.
|Name||Text||The descriptive name for the component.|
|Grouping Columns||List of Columns||Defines how the input data is grouped, this works like an SQL group by. 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. Note: 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 ouptut|
|First/Last||Return the First or Last Element of the chosen column in the grouped data.|
StrategyGenerates a select statement with a window function in line using the OVER keyword.
ExampleThis example returns the first flight for a given plane in a given year.
Note: The filter removes some null data rows.
The component properties are set up as below.
Note: 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.