Generate a dynamic sequence within a query

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

On Wed, Oct 20, 2010 at 3:30 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;
-
- 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

On Wed, Oct 20, 2010 at 4:07 PM, David Kerr <dmk@mr-paradox.net> 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;

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 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?

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