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

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,
"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.

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? Aug 12, 2021 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, 2021 at 13:27