From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 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 18:21:17 -0400 (EDT)
Message-ID: <200209172221.g8HMLH227467@candle.pha.pa.us> (raw)
In-Reply-To: <8957.1032300796@sss.pgh.pa.us>
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >>> 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.
>
> Say what? Given the ORDER BY in the subselect, it will.
>
> Of course, you can't do any grouping or other processing at the outer
> level, but the example as given is just fine.
When you specify a table in FROM, there is no ordering to the table. Is
it guaranteed that a subquery in FROM _does_ have an ordering. Does
ANSI say it has an ordering? What if the subquery is involved in a
join?
--
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, tgl@sss.pgh.pa.us, reedstrm@rice.edu, roland@astrofoto.org, yudie@axiontech.com
Subject: Re: How to select and result row number??
In-Reply-To: <200209172221.g8HMLH227467@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