X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 6C34A9F931D for ; Tue, 9 May 2006 05:54:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 28309-01 for ; Tue, 9 May 2006 05:54:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from titan.startnet.cz (unknown [82.113.63.73]) by postgresql.org (Postfix) with ESMTP id D6BC79FA226 for ; Tue, 9 May 2006 05:54:26 -0300 (ADT) Received: (qmail 23200 invoked from network); 9 May 2006 10:54:24 +0200 Received: from unknown (HELO ?192.168.1.200?) (miroslav.sulc@startnet.cz@10.15.192.25) by titan.local.startnet.cz with ESMTPA; 9 May 2006 10:54:24 +0200 Message-ID: <446058BB.5090201@startnet.cz> Date: Tue, 09 May 2006 10:54:19 +0200 From: =?UTF-8?B?TWlyb3NsYXYgxaB1bGM=?= Organization: StartNet s.r.o. User-Agent: Thunderbird 1.5.0.2 (X11/20060501) MIME-Version: 1.0 CC: pgsql-sql@postgresql.org Subject: Re: Most efficient way to hard-sort records References: <445C5A94.4020907@startnet.cz> In-Reply-To: Content-Type: multipart/mixed; boundary="------------050005050601070901010107" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/83 X-Sequence-Number: 25114 This is a multi-part message in MIME format. --------------050005050601070901010107 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Hi PFC, the generate_series() seems to be the right thing I need in my scenario but I didn't figure out how to create a SELECT stattement that will contain sorted main_table.id in first column and generated series in second column which I would use to update the main_table.position. Is it possible? How can I do that? Or is there another approach to employ generate_series()? Miroslav Šulc PFC napsal(a): > >> Another version along that line ? >> >> # create sequence counterseq start 1; >> -- (set/reset whenever a counter is needed) >> >> # select main_table.*, nextval('counterseq') as position2 >> into sorted_main_table >> from main_table, keytable where main_table.id = >> keytable.main_table_id >> order by value; > > You could also use generate_series(), but I don't know if it can > generate unbounded series... > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster --------------050005050601070901010107 Content-Type: text/x-vcard; charset=utf-8; name="miroslav.sulc.vcf" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="miroslav.sulc.vcf" YmVnaW46dmNhcmQNCmZuO3F1b3RlZC1wcmludGFibGU6TWlyb3NsYXYgPUM1PUEwdWxjDQpu O3F1b3RlZC1wcmludGFibGU6PUM1PUEwdWxjO01pcm9zbGF2DQpvcmc6U3RhcnROZXQgcy5y Lm8uDQphZHI7cXVvdGVkLXByaW50YWJsZTtxdW90ZWQtcHJpbnRhYmxlOjs7U2Nob2Rvdj1D Mz1BMSAzMDkvMTA7UHJhaGEgNTs7MTUwIDAwOz1DND04Q2Vzaz1DMz1BMSByZXB1Ymxpa2EN CmVtYWlsO2ludGVybmV0Om1pcm9zbGF2LnN1bGNAc3RhcnRuZXQuY3oNCnRlbDtjZWxsOis0 MjAgNjAzIDcxMSA0MTMNCngtbW96aWxsYS1odG1sOlRSVUUNCnVybDpodHRwOi8vd3d3LnN0 YXJ0bmV0LmN6DQp2ZXJzaW9uOjIuMQ0KZW5kOnZjYXJkDQoNCg== --------------050005050601070901010107--