From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Ross J. Reedstrom <reedstrm@rice.edu>
Cc: Roland Roberts <roland@astrofoto.org>
Cc: Yudie <yudie@axiontech.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: How to select and result row number??
Date: Tue, 17 Sep 2002 17:44:53 -0400 (EDT)
Message-ID: <200209172144.g8HLisd23301@candle.pha.pa.us> (raw)
In-Reply-To: <20020917144635.GB6538@rice.edu>
Ross J. Reedstrom wrote:
> On Mon, Sep 16, 2002 at 11:12:06PM -0400, Bruce Momjian wrote:
> > Yudie wrote:
> <wants numbered records>
>
> > Good question. The only easy answer I have is the creation of a temp
> > table with a SERIAL column:
> >
> > CREATE TEMP TABLE out (cnt SERIAL, other_cols...);
> > INSERT INTO out SELECT ... ORDER BY col;
>
> Hmm, this needs to be:
>
> INSERT INTO out (ther_cols...) SELECT ... ORDER BY col;
>
> So that the cnt column gets filled from the default.
Yes, thanks for the fix.
> > create sequence temp_counter;
> > select nextval('temp_counter'), * from whatever;
> >
> > If you have an 'order by', the above will not work. You could then
> > try either building a temporary table or using a subselect
> >
> > select nextval('temp_counter'), * from (select .... order by ...);
>
> Approximately the same solution, but without saving the result in a temp
> table.
I thought about doing it this way. However, a subselect as a
pseudotable is not guaranteed to return the data in any specific order,
so I don't think this method work work reliably. At least that was my
assumption.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgman@candle.pha.pa.us, reedstrm@rice.edu, roland@astrofoto.org, yudie@axiontech.com
Subject: Re: How to select and result row number??
In-Reply-To: <200209172144.g8HLisd23301@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox