My shapefile contains columns like "Street", "number" and "date".
What I want to do is, to find the latest date (where we have done house services). If we have 2 dates for one house number ("number"), we want to get rid of the older one (this feature should be deleted). I also added a column "duplicates" which shows 1 when we have more then one date for a house number.
Has anyone an idea how to select the latest dates (or how to delete the oldest ones) with the field calculator?
You can do this with the expression builder in Select by Expression.
Assuming your date is in a date format, try the following:
"duplicates" = 1 and "date" < to_date(maximum("date","number","number"="number"))
This should select all features where there is a duplicate and the date is less than the latest one for that house number.
However a faster calculation may be to select only the houses that have the latest date (which will include features where there are no duplicates):
"date" = to_date(maximum("date","number","number"="number"))
And then invert the feature selection.
If the above doesn't work as expected try removing the to_date() or also wrapping it around "date" in the beginning.
The maximum() aggregate function used with grouping and filtering arguments in QGIS's expression builder works much like max() with GROUP BY in SQL. In this case it groups all the records by house number, finds the maximum date for each house number, and then goes through the list of features and returns that maximum date for that house number (not the maximum date of all records!)
Please note you have to use maximum() in the expression builder and not max() (which is not an aggregate function).
I have tested this out in QGIS 3.4.
You can achieve this using an SQL query in QGIS DB Manager :
You choose Database / Database Manager / Database Manager then Virtual Layers / Qgis Layers you can then try the following query :
select max(date),street, number, id from services group by id
where id is your "house id". if you need geometry of objects you have to add the geometry field.
Then you can load this result as a layer in QGIS and save it if necessary.
