[postgis-users] Help with a selection query

[Martin Davis]

This is a nice application for windowing.  Try this:

WITH
data AS (SELECT * FROM (VALUES ( '902930' ), ( '902920' ),
    ( '902900' ), ( '903110' ),
    ( '913210' ), ( '913200' ) ) AS t(val)),
minsuf AS (SELECT val,
        MIN( right(val, 2)) OVER (PARTITION BY left(val, 4) ) AS suf
    FROM data)
SELECT val from minsuf
    WHERE suf <> '00' OR (suf = '00' AND right(val, 2) = '00');


On Thu, Jul 4, 2019 at 12:22 PM <paul.malm at lfv.se> wrote:

> I’ve sent this question to postgres user group without any success. Thats
> why I’m trying with you.
>
> I have a column named col (varchar)
>
> col could look like this
> 902930
> 902920
> 902900
> 903110
> 913210
> 913200
> …
> 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.
> All objects ending with 00 shall remain.
> All objects not ending with 00 shall remain if there is no object starting
> with the same 4 characters and ending with 00
>
> The result of the col table should be:
> 902900
> 903110
> 913200
>
> 903110 is not removed because there is no 903100
> 913210 is removed because there is a 913200
> 902930 and 902920 are removed because there is 902900
>
> I hope you understand the logic , perhaps there is no logic in my
> explanation.
> Thanks in advance,
> Paul
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190704/e2b36b37/attachment.html>