At 3.18.3, I am working with a standalone Geopackage attribute table containing data for a threatened bird species who are nesting in bird boxes.
The nesting boxes are periodically checked and the number of observed eggs are recorded. The data table looks like this:
box_id, observation_date, number_of_eggs:
1, 2021-05-17, 1
1, 2021-06-13, 0
2, 2021-05-17, 0
3, 2021-05-17, 1
3, 2021-06-09, NULL
3, 2021-06-13, 3
For each box_id I need to select the one record that contains the greatest number of eggs ever observed. Records with lesser, zero, or NULL egg values are to be ignored. If a box never had any eggs, it is likewise ignored.
In other words, did a given nest box produce eggs, and if so, what was the greatest number of eggs ever observed?
Using maximum("number_of_eggs",group_by:= "box_id") within Select by Expression, this is what I desire and expect (selected records are bolded):
1, 2021-05-17, 1
1, 2021-06-13, 0
2, 2021-05-17, 0
3, 2021-05-17, 1
3, 2021-06-09, NULL
3, 2021-06-13, 3
But this is what I see:
1, 2021-05-17, 1
1, 2021-06-13, 0
2, 2021-05-17, 0
3, 2021-05-17, 1
3, 2021-06-09, NULL
3, 2021-06-13, 3
It appears that the function is selecting all records for a given box_id, if that box_id ever contained one or more eggs.
How can I modify the expression to select just the single record with the maximum number of eggs for each box_id?
"number_of_eggs" = maximum("number_of_eggs",group_by:= "box_id"). At the moment its looks to me like the filter is selecting any rows where the box_id happens to equal the maximum number of eggs, ie the filter is set to"box_id" = maximum("number_of_eggs",group_by:= "box_id")- thats why box 2 is not picked up by the filter 2 != 0