Thread: Generate a dynamic sequence within a query

Generate a dynamic sequence within a query

From

David Kerr

Date:

20 October 2010, 22:22:57

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

Re: Generate a dynamic sequence within a query

From

Raymond O'Donnell

Date:

20 October 2010, 22:28:30

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

Re: Generate a dynamic sequence within a query

From

David Kerr

Date:

20 October 2010, 22:31:04

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

Re: Generate a dynamic sequence within a query

From

Date:

20 October 2010, 22:47:27

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

Re: Generate a dynamic sequence within a query

From

David Kerr

Date:

20 October 2010, 22:50:06

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

Re: Generate a dynamic sequence within a query

From

David Kerr

Date:

20 October 2010, 23:07:12

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

Re: Generate a dynamic sequence within a query

From

Date:

21 October 2010, 00:25:54

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

Re: Generate a dynamic sequence within a query

From

Josh Kupershmidt

Date:

21 October 2010, 02:32:43

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;

Re: Generate a dynamic sequence within a query

From

Darren Duncan

Date:

21 October 2010, 04:35:21

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

Re: Generate a dynamic sequence within a query

From

Alban Hertroys

Date:

21 October 2010, 11:43:53

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!

Re: Generate a dynamic sequence within a query

From

Thomas Kellerer

Date:

21 October 2010, 12:03:16

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

Re: Generate a dynamic sequence within a query

From

David Kerr

Date:

21 October 2010, 15:04:51

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

Re: Generate a dynamic sequence within a query

From

David Kerr

Date:

21 October 2010, 23:38:40

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

Re: Generate a dynamic sequence within a query

From

"Daniel Verite"

Date:

22 October 2010, 00:36:12

    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