Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 678D3474E49 for ; Mon, 18 Mar 2002 17:51:23 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id D62A8D5F7; Mon, 18 Mar 2002 14:51:24 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id C9D8D5BFD; Mon, 18 Mar 2002 14:51:24 -0800 (PST) Date: Mon, 18 Mar 2002 14:51:24 -0800 (PST) From: Stephan Szabo To: Hubert Palme Cc: Subject: Re: Line Numbering in SELRCT Output In-Reply-To: <15510.26490.674496.640496@palme-pc.urz.Uni-Wuppertal.DE> Message-ID: <20020318144733.E11746-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200203/225 X-Sequence-Number: 7104 On Mon, 18 Mar 2002, Hubert Palme wrote: > >>>>> "S" == Stephan Szabo writes: > > S> On Mon, 11 Mar 2002, Hubert Palme wrote: > >> Paul wrote: > >> > >> > CREATE SEQUENCE seq1; > >> > > >> > SELECT nextval('seq1'), amname FROM pg_am; > >> > > >> > DROP SEQUENCE seq1; > >> > > >> > >> Hmm... I forgot to mention that my SELECT statement has an ORDER BY clause. > >> The sort is done after the sequence numbers being generated. So the order > >> of the sequence numbers becomes damaged by the sort. > >> > >> Is there any hint how to avoid this? > > S> select nextval('seq1'), foo.* from (select ...) foo; > > I tried this: > >CREATE SEQUENCE z_nummer; >SELECT nextval('z_nummer'), hilfstab.name, hilfstab.vorname >FROM (SELECT * > FROM leute > ORDER BY name, vorname) hilfstab; > DROP SEQUENCE z_nummer; > > and got this result: > > mitglieder=> \i selorder.sql > CREATE > psql:selorder.sql:5: ERROR: parser: parse error at or near "select" > DROP > mitglieder=> > > Did I understand you right, this kind of subqueries in FROM clauses > are allowed? (Didn't find it in any manual) What version are you using? AFAIK that structure is allowed in 7.1 and after.