[postgis-users] Help with a selection query

[paul.malm at lfv.se]

Thanks, Hugues and Martin for helping a SQL newbee!

5 juli 2019 kl. 00:02 skrev Martin Davis <mtnclimb at gmail.com<mailto:mtnclimb at gmail.com>>:

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<mailto: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<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/postgis-users