7

At 3.22.1 I have a GeoPackage attribute table containing the string field: box_id and the date field: inspection_date. The table rows look like:

box_id         inspection_date
NE_01_01    2021-05-01
NE_01_01    2021-05-02
NE_01_01    2021-05-04
SE_03_02     2021-06-09
SW_01_04    2021-06-05
SW_01_04    2021-06-07
...

I need to select those records with the latest inspection_date for each box_id. Using my example data, the resulting selection would look like:

box_id         inspection_date
NE_01_01    2021-05-04
SE_03_02     2021-06-09
SW_01_04    2021-06-07
...

I have tried this selection expression:

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

but it returns zero records.

What am I doing wrong?

NOTE:

Following the answer by Kadir Sahbaz, I did some experimentation and found that converting inspection_date from a date type to a string type also worked:

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

Apparently maximum() requires a string type, not a date type.

3 Answers 3

10

QDateTimeobject is returned from maximum, but inspection_date is probably QDate. And to_date converts the result to QDate object.

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

Using arrays seems to work without further modification (>= QGIS 3.18)

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

enter image description here

0
7

Another option (if you want to create a separate layer) is to use a Virtual Layer:

select "key", "type", max("datefield") as latestdate, st_pointonsurface("geometry") as geom --Or just "geometry" if you want the original geometry, I want a point
from Refactored
group by "type"

enter image description here

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.