Select text field associated with maximum numeric field during QGIS aggregate process

Question

I have a site layer and a grid layer and want to add attributes to the site layer which include a new area (the intersection) and the grid reference (a text field) associated with the maximum intersection .

Using: Calculate area of intersecting polygons in QGIS

I have used Union to split by sites by the grids, allowing me to generate the intersection area. I am now using Aggregate to recombine the features based on a unique SiteID while preserving the maximum intersection area value.

Aggregate allows me to select the maximum area (100mArea) but i can't see a clear way to select the grid reference associated with that maximum area (GridRef_2, from the same feature). Is it possible to use a different field within a function to select the corresponding text field?

As a fall back I could do this in Excel by removing all rows bar the maximum area for each siteID then re-joining the table but it seems like something that should be possible in QGIS..

Answer 1

Use this expression as the source expression to calculate the GridRef_2 field and use first_value or last_value as the aggregate function.

attribute(
    get_feature(
        @layer,
        '100mArea',
        maximum("100mArea")
    ),
    'GridRef_2'
)