Re: Generate a dynamic sequence within a query

On Wed, Oct 20, 2010 at 03:47:19PM -0700, DM wrote:
- select generate_series(1,(select count(*) from tax)), country from tax;
-
- you should use braces around the sub select.
-
- Thanks
- Deepak

Table "public.test"
Column | Type | Modifiers
--------+----------------------+-----------
col1 | character varying(2) |
col2 | character varying(2) |

select * from test;
col1 | col2
------+------
A | A
A | B
A | C
B | A
B | B
B | C
(6 rows)

select generate_series(1,(select count(*) from test)), col1, col2 from test;
generate_series | col1 | col2
-----------------+------+------
1 | A | A
2 | A | A
3 | A | A
4 | A | A
5 | A | A
6 | A | A
1 | A | B
2 | A | B
3 | A | B
4 | A | B
5 | A | B
6 | A | B
1 | A | C
2 | A | C
3 | A | C
4 | A | C
5 | A | C
6 | A | C
1 | B | A
2 | B | A
3 | B | A
4 | B | A
5 | B | A
6 | B | A
1 | B | B
2 | B | B
3 | B | B
4 | B | B
5 | B | B
6 | B | B
1 | B | C
2 | B | C
3 | B | C
4 | B | C
5 | B | C
6 | B | C
(36 rows)

when what i want is:
1 | A | A
2 | A | B
3 | A | C
4 | B | A
5 | B | B
6 | B | C

thanks

Dave

-
- On Wed, Oct 20, 2010 at 3:30 PM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:
-
- > On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:
- > - On 20/10/2010 23:22, David Kerr wrote:
- > - >I know I've seen posts on how to do this, but i can't seem to find them.
- > - >
- > - >I've got a data set
- > - >
- > - >A, B
- > - >A, C
- > - >A, D
- > - >[...]
- > - >
- > - >and so on
- > - >
- > - >and i'd like to be able to wite a query that would result in
- > - >
- > - >1,A,B
- > - >2,A,C
- > - >3,A,D
- > - >[...]
- > - >
- > - >PG version is 8.3.
- > - >
- > - >Any ideas?
- > -
- > - You probably want generate_series():
- > -
- > - http://www.postgresql.org/docs/8.3/static/functions-srf.html
- > -
- > - Ray.
- >
- > I thought, so. what would that look like?
- >
- > select generate_series(1,select count(*) from table), field1, field2 from
- > table
- > doesn't work..
- >
- >
- > thanks
- >
- > Dave
- >
- > --
- > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
- > To make changes to your subscription:
- > http://www.postgresql.org/mailpref/pgsql-general
- >