Create mutliple rows from a field

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

Create mutliple rows from a field

Olivier Leprêtre

Hi,

 

I have a table with 2 columns : id, x (x is an int from 0 to n)

 

Is it possible to create a query that will return x ranked rows for each id ?

 

with

 

id       x

A        2

B        3

C        1

D       4

 

will return

 

A        1

A        2

B        1

B        2

B        3

C        1

D       1

D       2

D       3

D       4

 

I searched with OVER, PARTITION, RANK... without success

 

Thanks,

 

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: Create mutliple rows from a field

Nathan Wagner
On Thu, May 18, 2017 at 07:05:39PM +0200, Olivier Lepr?tre wrote:

[ excess newlines snipped]

> I have a table with 2 columns : id, x (x is an int from 0 to n)

> Is it possible to create a query that will return x ranked rows for
> each id ?

> with

> id       x
> A        2
> B        3
> C        1
> D       4

> will return

> A        1
> A        2
> B        1
> B        2
> B        3
> C        1
> D       1
> D       2
> D       3
> D       4

create table foo (id text, x integer);
insert into foo
values ('A',2),('B',3),('C',1),('D',4)
;
select id, generate_series(1, x) as r from foo;

--
nw
_______________________________________________
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: Create mutliple rows from a field

Mickael Borne
In reply to this post by Olivier Leprêtre
Hi,

You should have a look to generate_series :

https://www.postgresql.org/docs/9.5/static/functions-srf.html



De : postgis-users [[hidden email]] de la part de Olivier Leprêtre [[hidden email]]
Date d'envoi : jeudi 18 mai 2017 19:05
À : 'PostGIS Users Discussion'
Objet : [postgis-users] Create mutliple rows from a field

Hi,

 

I have a table with 2 columns : id, x (x is an int from 0 to n)

 

Is it possible to create a query that will return x ranked rows for each id ?

 

with

 

id       x

A        2

B        3

C        1

D       4

 

will return

 

A        1

A        2

B        1

B        2

B        3

C        1

D       1

D       2

D       3

D       4

 

I searched with OVER, PARTITION, RANK... without success

 

Thanks,

 

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: Create mutliple rows from a field

Olivier Leprêtre
In reply to this post by Nathan Wagner
Thanks very much Nathan and Mickael for those quick and good answers !

Olivier
-----Message d'origine-----
De : postgis-users [mailto:[hidden email]] De la part de Nathan Wagner
Envoyé : jeudi 18 mai 2017 19:21
À : PostGIS Users Discussion
Objet : Re: [postgis-users] Create mutliple rows from a field

On Thu, May 18, 2017 at 07:05:39PM +0200, Olivier Lepr?tre wrote:

[ excess newlines snipped]

> I have a table with 2 columns : id, x (x is an int from 0 to n)

> Is it possible to create a query that will return x ranked rows for
> each id ?

> with

> id       x
> A        2
> B        3
> C        1
> D       4

> will return

> A        1
> A        2
> B        1
> B        2
> B        3
> C        1
> D       1
> D       2
> D       3
> D       4

create table foo (id text, x integer);
insert into foo
values ('A',2),('B',3),('C',1),('D',4)
;
select id, generate_series(1, x) as r from foo;

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


---
L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast.
https://www.avast.com/antivirus

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