[postgis-users] add group number to a group by clause

[James Keener]

You could try something with a window function. Something like case when
lag(x) = x then y else y + 1 end

On Thu, Jul 20, 2017 at 1:53 PM, Olivier Leprêtre <o.lepretre at gmail.com>
wrote:

> Hi,
>
>
>
> 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.
>
>
>
> Here is the query :
>
>
>
> select o.nbre,d.code,d.numero,d.nomvoie,d.commune from
>
> (select geom,count(*) as nbre from adress group by geom having count(*)
> >1) as o,
>
>          lateral (select * from adress) as d where st_within(o.geom,d.geom)
>
>
>
> it returns the piled points preceded with piled points count.
>
>
>
> 3 pointa (3 piled points)
>
> 3 pointf
>
> 3 pointg
>
> 2 point1 (2 pp)
>
> 2 point2
>
> 4 pntw   (4 pp)
>
> 4 pntx
>
> 4 pnty
>
> 4 pntz
>
>
>
> How can I add a group column like this ?
>
>
>
> 1        3 pointa
>
> 1        3 pointf
>
> 1        3 pointg
>
> 2        2 point1
>
> 2        2 point2
>
> 3        4 pntw
>
> 3        4 pntx
>
> 3        4 pnty
>
> 3        4 pntz
>
>
>
>
>
> Thanks for any idea,
>
>
>
> Olivier
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Garanti
> sans virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
> <#m_-1405050486293552671_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>
> _______________________________________________
> 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/20170720/1d7ebd31/attachment.html>