<div dir="ltr">You could try something with a window function. Something like case when lag(x) = x then y else y + 1 end<br></div><div class="gmail_extra"><br><div class="gmail_quote">On Thu, Jul 20, 2017 at 1:53 PM, Olivier Leprêtre <span dir="ltr"><<a href="mailto:o.lepretre@gmail.com" target="_blank">o.lepretre@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div link="blue" vlink="purple" lang="FR"><div class="m_-1405050486293552671WordSection1"><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Hi,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">I have sets of points which are piled up by groups. I found how to show the different groups with the query below but I didn't find how to add a group number for each group. I tried with "over partition" but it seems that it's not possible to partition with geom column.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Here is the query :<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">select o.nbre,d.code,d.numero,d.<wbr>nomvoie,d.commune from<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">(select geom,count(*) as nbre from adress group by geom having count(*) >1) as o,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">         lateral (select * from adress) as d where st_within(o.geom,d.geom)<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">it returns the piled points preceded with piled points count.<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">3 pointa (3 piled points)<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">3 pointf<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">3 pointg<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">2 point1 (2 pp)<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">2 point2<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">4 pntw   (4 pp)<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">4 pntx<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">4 pnty<u></u><u></u></span></p><p class="MsoNormal" style="margin-left:35.4pt"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">4 pntz<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">How can I add a group column like this ?<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">1        3 pointa<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">1        3 pointf<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">1        3 pointg<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">2        2 point1<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">2        2 point2<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">3        4 pntw<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">3        4 pntx<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">3        4 pnty<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">3        4 pntz<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Thanks for any idea,<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US">Olivier<u></u><u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span style="font-size:10.0pt;font-family:"Verdana","sans-serif"" lang="EN-US"><u></u> <u></u></span></p><p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p></div><div id="m_-1405050486293552671DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2"><br> <table style="border-top:1px solid #d3d4de">
        <tbody><tr>
      <td style="width:55px;padding-top:18px"><a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" target="_blank"><img src="https://ipmcdn.avast.com/images/icons/icon-envelope-tick-round-orange-animated-no-repeat-v1.gif" alt="" style="width:46px;height:29px" width="46" height="29"></a></td>
                <td style="width:470px;padding-top:17px;color:#41424e;font-size:13px;font-family:Arial,Helvetica,sans-serif;line-height:18px">Garanti sans virus. <a href="https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient" style="color:#4453ea" target="_blank">www.avast.com</a>               </td>
        </tr>
</tbody></table>
<a href="#m_-1405050486293552671_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2" width="1" height="1"> </a></div></div><br>______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">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/<wbr>mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>