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)?


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,
FROM   (
         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.


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).

  • thanks for your sugestion, Stefan. But I get an error:; ERROR: syntax error at or near "LEFT" LINE 3: LEFT INNER JOIN fillspill_pol_value b ON a.id_pol, b.id_pol; And could you please explain why you have added the b into the expression?
    – heljor
    Aug 12 '21 at 12:56
  • My bad, according to docs, there is no LEFT INNER JOIN. :D I have updated my answer. a and b are needed to distinguish the left table from the right one since we are effectively joining the table to itself. Aug 12 '21 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.