From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig A. James <cjames@modgraph-usa.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Sort order in sub-select
Date: Fri, 30 Jun 2006 02:21:06 -0400
Message-ID: <20650.1151648466@sss.pgh.pa.us> (raw)
In-Reply-To: <44A4AEAE.8070809@modgraph-usa.com>
References: <44A4AEAE.8070809@modgraph-usa.com>
"Craig A. James" <cjames@modgraph-usa.com> writes:
> insert into hitlist(p_id, sortorder)
> (select p_id, nextval('hitlist_seq') from
> (select p_id, min(data) as m from c group by p_id order by m);
> Apparently, the sort order returned by the innermost select is NOT
> maintained as you go through the next select statement -- the rows seem
> to come out in random order. This surprised me.
It surprises me too. This is outside the SQL spec, because the spec
doesn't allow ORDER BY in subselects, but Postgres definitely does and
we expect it to be honored. Can you provide a complete example and the
EXPLAIN plan that you're getting?
regards, tom lane
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-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, cjames@modgraph-usa.com
Subject: Re: Sort order in sub-select
In-Reply-To: <20650.1151648466@sss.pgh.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