I'm working on a query that I think could fit under the greatest-n-per-group category.
So here's a simplified version of the table:
So these are samples taken with a ~10 year span. So I have the exact same geometry stored in two or more rows.
I'd like to fetch only one row for each unique geometry, and the hard part is that I only want the latest taken samples (according to year column) and the data on that row.
I have tried something like this:
SELECT * FROM samples s INNER JOIN ( SELECT geom, MAX(year) as year FROM samples GROUP BY geom ) b ON ST_EQUALS(s.geom, b.geom) AND s.year = b.year WHERE customer_id = 38330 ORDER BY provnr ASC;
This seems to give me a pretty good result, but it takes a lot of time. This specific customer has ~850 rows in a table of ~400k rows. That query takes about 7 seconds. I have the geom and year columns indexed.
Is there a better way to write the query? The query will be used in a real time app, so 7 seconds is way to long.
Really thankful for your'e help!