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.

Improve this question

3 Answers 3

Reset to default
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"))
Improve this answer
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

Improve this answer
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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.