Converting attribute table expression to filter expression using QGIS

filterqgis

In Selecting latest record by date about selecting the latest attribute table record, grouped by a field value, three different selection expressions were offered as solutions. All three worked correctly, and here they are:

"inspection_date" = maximum(format_date("inspection_date", 'yyyy-MM-dd'), group_by:="box_id")

"inspection_date" = to_date(maximum("inspection_date", group_by:="box_id"))

"inspection_date" = array_max(array_agg("inspection_date", group_by:="box_id"))

The three expressions were similar in that they 1) recognized a maximum date, and 2) were able to group by the box_id field.

Now I would like to apply the same selection criteria as a filter expression. However, filter expressions use a different syntax (QGR SQL dialect) than attribute table selections, so I cannot utilize any of the three expressions above. My SQL skills are pretty limited, so I reviewed the OGR SQL page. Unfortunately, I did not see any way to select a maximum date, nor to group that maximum date by an attribute field.

How can I filter an attribute table to include only the latest record, grouped according to the values in an attribute field?

Best Answer

As shown in my other solution, you can filter directly, without using SQL queries.

If, however, you still want to filter with SQL, proceed as follows:

  1. Create a new field (virtual field if you want it to update dynamically) with Fild calculator and one of the expressions you provided. Call the field condition and use Boolean (true/false) as field type.

  2. Filter with an SQL query like condition = true

Related Question