Обсуждение: Generate a dynamic sequence within a query
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? Thanks Dave
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.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
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
select generate_series(1,(select count(*) from tax)), country from tax;
you should use braces around the sub select.
Thanks
Deepak
you should use braces around the sub select.
Thanks
Deepak
On Wed, Oct 20, 2010 at 3:30 PM, David Kerr <dmk@mr-paradox.net> wrote:
On Wed, Oct 20, 2010 at 11:28:18PM +0100, Raymond O'Donnell wrote:I thought, so. what would that look like?
- 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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 - Ah, great, thanks! Dave - On Wed, Oct 20, 2010 at 3:30 PM, David Kerr <dmk@mr-paradox.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@postgresql.org) - > To make changes to your subscription: - > http://www.postgresql.org/mailpref/pgsql-general - >
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@mr-paradox.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@postgresql.org)
- > To make changes to your subscription:
- > http://www.postgresql.org/mailpref/pgsql-general
- >
create temp table dup_test (nm1 varchar(2),nm2 varchar(3));
insert into dup_test values ('A','A'),('A','B'),('A','C'),('B','A'),('B', 'B'),('B','C');
CREATE SEQUENCE dup_test_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
alter table dup_test add column dup_id integer;
alter table dup_test alter column dup_id SET DEFAULT nextval('dup_test_seq'::regclass);
update dup_test set dup_id = nextval('dup_test_seq'::regclass);
select * from dup_test;
nm1 | nm2 | dup_id
-----+-----+--------
A | A | 1
A | B | 2
A | C | 3
B | A | 4
B | B | 5
B | C | 6
(6 rows)
Hope this helps
insert into dup_test values ('A','A'),('A','B'),('A','C'),('B','A'),('B', 'B'),('B','C');
CREATE SEQUENCE dup_test_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1
CACHE 1;
alter table dup_test add column dup_id integer;
alter table dup_test alter column dup_id SET DEFAULT nextval('dup_test_seq'::regclass);
update dup_test set dup_id = nextval('dup_test_seq'::regclass);
select * from dup_test;
nm1 | nm2 | dup_id
-----+-----+--------
A | A | 1
A | B | 2
A | C | 3
B | A | 4
B | B | 5
B | C | 6
(6 rows)
Hope this helps
On Wed, Oct 20, 2010 at 4:07 PM, David Kerr <dmk@mr-paradox.net> wrote:
On Wed, Oct 20, 2010 at 03:47:19PM -0700, DM wrote:- select generate_series(1,(select count(*) from tax)), country from tax;-Table "public.test"
- you should use braces around the sub select.
-
- Thanks
- Deepak
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@mr-paradox.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@postgresql.org)
- > To make changes to your subscription:
- > http://www.postgresql.org/mailpref/pgsql-general
On Wed, Oct 20, 2010 at 6:22 PM, David Kerr <dmk@mr-paradox.net> 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. If you can upgrade to 8.4, you could use the row_number() window function which is perfectly suited to this task, should be as simple as: SELECT row_number() OVER (), * FROM tablename;
Josh Kupershmidt wrote: > On Wed, Oct 20, 2010 at 6:22 PM, David Kerr <dmk@mr-paradox.net> 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. > > If you can upgrade to 8.4, you could use the row_number() window > function which is perfectly suited to this task, should be as simple > as: > > SELECT row_number() OVER (), * FROM tablename; Yes indeed. For a simple increment by one sequence, functions like rank() ... see http://www.postgresql.org/docs/9.0/interactive/functions-window.html ... are exactly what you want. -- Darren Duncan
On 21 Oct 2010, at 24:28, Raymond O'Donnell wrote: >> 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 I'm currently using WebFOCUS at work and they have a LAST operator, referring to the value a column had in the last returnedrow. That's pretty good for stuff like this, so I wonder if it wouldn't be beneficial to have something like thatin Postgres? SQL isn't FOCUS, but in SQL it would work something like this: SELECT COALESCE(LAST foo +1, 1) AS foo, bar FROM table; foo | bar ----+----- 1 | Apple 2 | Banana 3 | Orange 4 | Lemon Or for fun, SELECT COALESCE(LAST foo *2, 1) AS foo, bar || COALESCE(LAST bar, '') AS bar FROM table; foo | bar ----+------------------------ 1 | Apple 2 | BananaApple 4 | OrangeBananaApple 8 | LemonOrangeBananaApple Of course being able to use LAST requires that there's still a copy of the last returned row lingering in a buffer somewhere.If we have that, great! If we don't, well, it depends on how much the devs desire such a feature :) Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4cc0277010283330040792!
Alban Hertroys, 21.10.2010 13:43: > I'm currently using WebFOCUS at work and they have a LAST operator, > referring to the value a column had in the last returned row. That's > pretty good for stuff like this, so I wonder if it wouldn't be > beneficial to have something like that in Postgres? Already there since 8.4 ;) Look into the windowing functions (in Oracle they are called analytical functions) http://www.postgresql.org/docs/current/static/tutorial-window.html Thomas
On Wed, Oct 20, 2010 at 10:32:15PM -0400, Josh Kupershmidt wrote: - On Wed, Oct 20, 2010 at 6:22 PM, David Kerr <dmk@mr-paradox.net> 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. - - If you can upgrade to 8.4, you could use the row_number() window - function which is perfectly suited to this task, should be as simple - as: - - SELECT row_number() OVER (), * FROM tablename; Ah, no chance of that for a while. figures all the fun stuff is always a version away =) Dave
On Wed, Oct 20, 2010 at 09:35:11PM -0700, Darren Duncan wrote: - Josh Kupershmidt wrote: - >On Wed, Oct 20, 2010 at 6:22 PM, David Kerr <dmk@mr-paradox.net> 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. - > - >If you can upgrade to 8.4, you could use the row_number() window - >function which is perfectly suited to this task, should be as simple - >as: - > - >SELECT row_number() OVER (), * FROM tablename; - - Yes indeed. For a simple increment by one sequence, functions like rank() - ... see - http://www.postgresql.org/docs/9.0/interactive/functions-window.html ... - are exactly what you want. -- Darren Duncan Well, an upgrade's not on tap for a few months. Until then i'll need to figure out somethnig else. thanks all. Dave
David Kerr wrote: > Well, an upgrade's not on tap for a few months. Until then i'll need to > figure out somethnig else. This may help: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-on e-query/ or http://preview.tinyurl.com/mc4q6p Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org