Get max value from points based on distinct id, and keep geometry for the new selection

Question

I have a pointfile of approx.. 226000 points, where I need to filter by max value and distinct id_pol.

SELECT  distinct (id_pol), MAX(value)
FROM    fillspill_pol_value
GROUP BY id_pol;

This expression gives me 460 rows, which is correct, but when I add geometry to the selection, it gives me 226000 rows. How can I select only the geometry for the 460 points (the max values)?

Answer 1

If d_pol is not unique across geom, and thus you want to select the [row with the] geom having the maximum value within each id_pol group, you need to select them from a ranked sub-query:

SELECT id_pol,
       "value",
       geom
FROM   (
  SELECT *,
         ROW_NUMBER() OVER(PARTITION BY id_pol ORDER BY "value" DESC) AS _rank
  FROM   fillspill_pol_value
) q
WHERE  _rank = 1
;

ROW_NUMBER assigns a _rank in descending order of "value" per group (window) of id_pol; see Window Functions for details.

Answer 2

You should be able to join the geometries to the selected values:

SELECT distinct (a.id_pol), MAX(a.value), b.geometry
FROM fillspill_pol_value a GROUP BY id_pol
LEFT JOIN fillspill_pol_value b ON a.id_pol, b.id_pol;

Since we are joining the table fillspill_pol_value to itself, we must distinguish the left and right table by giving it names (e.g. a and b in my example).