<div dir="ltr">This is a nice application for windowing. Try this:<div><br></div><div>WITH <br>data AS (SELECT * FROM (VALUES ( '902930' ), ( '902920' ),<br> ( '902900' ), ( '903110' ),<br> ( '913210' ), ( '913200' ) ) AS t(val)),<br>minsuf AS (SELECT val, <br> MIN( right(val, 2)) OVER (PARTITION BY left(val, 4) ) AS suf <br> FROM data)<br>SELECT val from minsuf <br> WHERE suf <> '00' OR (suf = '00' AND right(val, 2) = '00');<br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Jul 4, 2019 at 12:22 PM <<a href="mailto:paul.malm@lfv.se">paul.malm@lfv.se</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I’ve sent this question to postgres user group without any success. Thats why I’m trying with you.<br>
<br>
I have a column named col (varchar)<br>
<br>
col could look like this<br>
902930<br>
902920<br>
902900<br>
903110<br>
913210<br>
913200<br>
…<br>
I would like to remove an object that doesn’t end ‘00’ and if there are objects that start with the same 4 charcters and ending with 00.<br>
All objects ending with 00 shall remain.<br>
All objects not ending with 00 shall remain if there is no object starting with the same 4 characters and ending with 00 <br>
<br>
The result of the col table should be:<br>
902900<br>
903110<br>
913200<br>
<br>
903110 is not removed because there is no 903100<br>
913210 is removed because there is a 913200<br>
902930 and 902920 are removed because there is 902900<br>
<br>
I hope you understand the logic , perhaps there is no logic in my explanation.<br>
Thanks in advance,<br>
Paul<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>