From: | Andrew Sullivan <andrew(at)libertyrms(dot)info> |
---|---|
To: | PostgreSQL general list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Probably simple answer |
Date: | 2001-11-06 18:12:27 |
Message-ID: | 20011106131227.F1544@mail.libertyrms.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brent R. Matzelle | 2001-11-06 18:24:26 | Re: Perfomance Problems |
Previous Message | Vivek Khera | 2001-11-06 18:02:13 | Re: postgres 7.1.1 on Freebsd 4.3? |