[postgis-users] Help with a selection query

Hugues François hugues.francois at irstea.fr
Thu Jul 4 12:32:26 PDT 2019

An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190704/8e623cdb/attachment.html>
-------------- next part --------------
You may want to use left and right string Function. 
First count the occurrence number of the first four character using group by.
Those which have a count >1 will be the one of.interest for the second step: keep only those which ends by 00. 
Third keep those which have a count >1 but which are not in the second step. 
I guess you will have to use several "with" subqueries to achieve that in one time...
Best regards,
Le 4 juil. 2019 9:22 PM, paul.malm at lfv.se a écrit :

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
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:
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,
postgis-users mailing list
postgis-users at lists.osgeo.org

More information about the postgis-users mailing list