3

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?

3 Answers 3

5

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

4

There's no need to "translate" the QGIS expression to an SQL query. You can simply use the selection, created with one of the expressions you provided, to filter based on the selection using the menu at the bottom left of the attribute table - see screenshot.

Or, even bettet, use the Advanced Filter (Expression) in the same menu and paste one of your expressions.

enter image description here

1
  • Thanks, but what you propose is a selection, which (in your example) results in three of the six records highlighted. This is different than the Layer Properties > Source > Provider Feature Filter approach, which would result in three total features. I'm interested in the latter.
    – Stu Smith
    Dec 26, 2021 at 22:55
3

In general in SQL, your query would be

SELECT box_id,MAX(inspection_date) GROUP BY box_id

Given that inspection_date either is a date field or a text field that makes sense for sorting, e.g. a format like YYYYMMDD.

The bad news in your case is that OGR SQL does not seem to have the GROUP BY (ogrinfo SQL GROUP BY Clause) unless your data source supports it, so if your data is stored e.g. in postGIS, the GROUP BY should work, if it is e.g. in a shape file, I am afraid you will have to go through some other method.

1
  • Thanks. I'm using a Geopackage as my data source. I couldn't get your query to work, so I assume that it has to do with the Geopackage source. OTOH, I'm SQL illiterate, so I may be the problem.
    – Stu Smith
    Dec 26, 2021 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.