add group number to a group by clause

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

add group number to a group by clause

Olivier Leprêtre

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

 

 

 

 

 

 


Garanti sans virus. www.avast.com

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: 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 <[hidden email]> 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

 

 

 

 

 

 


Garanti sans virus. www.avast.com

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...