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(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110