Accounts listing aggregation issues

I have a view that lists accounts within financial entities, and want to show the most recent statement using views aggregation.

I'm running in to a handful of issues with this.

To get started, add a date field to Article and use Devel Generate for some dummy content.

Then create a view that lists article titles in a table, with no sort.

Here is the query that is generated.

SELECT 
  node_field_data.nid 
    AS nid 
FROM 
  node_field_data node_field_data 
WHERE 
  node_field_data.type 
    IN ('article')

Now enable aggregation.

SELECT 
  MIN(node_field_data.nid) 
    AS nid 
FROM 
  node_field_data node_field_data 
WHERE 
  node_field_data.type 
    IN ('article')

With the nid now in MIN(), the lowest nid content is the only item shown.

View
These are the default aggregation settings for the title field.

Grouping by entity ID seems to mean "get the lowest nid content".

The other option is to group by value, which will list all the titles.

SELECT 
  node_field_data.title 
    AS node_field_data_title, 
  MIN(node_field_data.nid) 
    AS nid 
FROM 
  node_field_data node_field_data 
WHERE 
  node_field_data.type 
    IN ('article') 
GROUP BY 
  node_field_data_title

This adds the title value to the select list and group by, so keep it on entity ID.

Here's where things get tricky. Add the date field, and use maximum for the aggregation type.

Now there's a lot of extra things in the query.

SELECT 
  MAX(
    node__field_date.field_date_value
  ) 
    AS node__field_date_field_date_value, 
  MAX(node__field_date.delta) 
    AS node__field_date_delta, 
  MAX(node__field_date.langcode) 
    AS node__field_date_langcode, 
  MAX(node__field_date.bundle) 
    AS node__field_date_bundle, 
  MIN(node_field_data.nid) 
    AS nid 
FROM 
  node_field_data node_field_data 
  LEFT JOIN node__field_date node__field_date 
    ON node_field_data.nid = node__field_date.entity_id 
  AND node__field_date.deleted = '0' Views Aggregator Plus module
WHERE 
  node_field_data.type 
    IN ('article')

Issues

Views Aggregator Plus module

Configured a view with fields for the content type (set to group and compress in table aggregation), integer (set to max), title (display first, hidden), and a custom text field.

Sort by the date field, descending.

Use the hidden title token in the custom text to print the title with a link. Otherwise, the aggregation will replace it.

There's a bug related to this: https://www.drupal.org/project/views_aggregator/issues/3279959

And a bug that fixes the module (was broken otherwise): https://www.drupal.org/project/views_aggregator/issues/3482129

The SQL query is just sorting by date, and the display filters the results for the max.

SELECT 
  node__field_integer.field_integer_value 
    AS node__field_integer_field_integer_value, 
  node_field_data.nid 
    AS nid 
FROM 
  node_field_data node_field_data 
  LEFT JOIN node__field_integer node__field_integer 
    ON node_field_data.nid = node__field_integer.entity_id 
  AND node__field_integer.deleted = '0' 
WHERE 
  (node_field_data.status = '1') 
  AND (
    node_field_data.type 
    IN ('article')
  ) 
ORDER BY 
  node__field_integer_field_integer_value DESC