Re: Probably simple answer

On Tue, Nov 06, 2001 at 07:41:06AM -0800, Andrew Gould wrote:
> I think he simply wants the rows of the result set
> numbered from 1 to whatever the last row is.  This is
> useful for referencing rows when discussing reports.
> It is also an immense help when calculating a record's
> statistical percentile ranking.
>
> Doing it in a table is no problem.  But how do you
> create one dynamically in a select query?

What about using a sequence inside a transaction:

scratch=# \d tmp1
        Table "tmp1"
 Attribute | Type | Modifier
-----------+------+----------
 col1      | text |

scratch=# begin ;
BEGIN
scratch=# create SEQUENCE temp_seq minvalue 1 increment 1;
CREATE
scratch=# select nextval('temp_seq') as rownum, col1 from tmp1 ;
 rownum | col1
--------+------
      1 | a
      2 | b
      3 | c
      4 | d
      5 | e
      6 | f
(6 rows)

scratch=# rollback;
ROLLBACK

The ROLLBACK gets rid of the sequence, so you don't have it hanging
around, and since you're in a transaction, no-one else can see your
sequence, so it won't get incremented by someone else calling to it.
Not perfect, but for on-the-fly row numbering, it might work.

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110