1 NEW YORK<= /DIV>

2 LOS ANGELES

3 HOUSTON

4 ....

5 ...

Thank you.

Yudie

------=_NextPart_000_000E_01C25B30.41545980-- From pgman@candle.pha.pa.us Tue Sep 17 03:12:23 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BC564475E2C for ; Mon, 16 Sep 2002 23:12:23 -0400 (EDT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id F1E36475C15 for ; Mon, 16 Sep 2002 23:12:21 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g8H3C6s16543; Mon, 16 Sep 2002 23:12:06 -0400 (EDT) From: Bruce Momjian Message-Id: <200209170312.g8H3C6s16543@candle.pha.pa.us> Subject: Re: How to select and result row number?? In-Reply-To: <001301c25b5a$2ef0ea40$8a01a8c0@yudie> To: Yudie Date: Mon, 16 Sep 2002 23:12:06 -0400 (EDT) Cc: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/187 X-Sequence-Number: 9464 Yudie wrote: > Hi, > > I would like to select and also give numbering in each record. > Let say I have table 'City' with a 'cityname' column > > what is the select statement so I can get result records like this > > no cityname > ---- -------------- > 1 NEW YORK > 2 LOS ANGELES > 3 HOUSTON > 4 .... > 5 ... 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; SELECT * FROM out; -- 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 From roland@astrofoto.org Tue Sep 17 14:14:55 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id BDAA84763F7 for ; Tue, 17 Sep 2002 10:14:55 -0400 (EDT) Received: from mail.astrofoto.org (dsl092-102-177.nyc2.dsl.speakeasy.net [66.92.102.177]) by postgresql.org (Postfix) with ESMTP id 11C4D4763EA for ; Tue, 17 Sep 2002 10:14:55 -0400 (EDT) Received: from kuiper.rlent.pnet (IDENT:root@kuiper.rlent.pnet [192.168.3.50]) by mail.astrofoto.org (8.12.3/8.12.3) with ESMTP id g8HEExtV004570 for ; Tue, 17 Sep 2002 10:14:59 -0400 Received: (from roland@localhost) by kuiper.rlent.pnet (8.11.6/8.11.6) id g8HEEwc02519; Tue, 17 Sep 2002 10:14:58 -0400 X-Authentication-Warning: kuiper.rlent.pnet: roland set sender to roland@astrofoto.org using -f To: Subject: Re: How to select and result row number?? References: <001301c25b5a$2ef0ea40$8a01a8c0@yudie> From: Roland Roberts Date: 17 Sep 2002 10:14:58 -0400 In-Reply-To: <001301c25b5a$2ef0ea40$8a01a8c0@yudie> Message-ID: Lines: 30 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2.90 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/191 X-Sequence-Number: 9468 >>>>> "Yudie" == Yudie writes: Yudie> what is the select statement so I can get result records like this Yudie> no cityname Yudie> ---- -------------- Yudie> 1 NEW YORK Yudie> 2 LOS ANGELES Yudie> 3 HOUSTON Yudie> 4 .... Yudie> 5 ... This is not really a SQL type result; you are probably better of writing something in a procedural language. That said, something like this seems to work for 7.2: 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 ...); roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises roland@rlenter.com 76-15 113th Street, Apt 3B roland@astrofoto.org Forest Hills, NY 11375 From reedstrm@rice.edu Tue Sep 17 14:46:33 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id EF1314767D8 for ; Tue, 17 Sep 2002 10:46:33 -0400 (EDT) Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (Postfix) with ESMTP id 3ABCF4767D4 for ; Tue, 17 Sep 2002 10:46:33 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 0B02D68A6B; Tue, 17 Sep 2002 09:46:36 -0500 (CDT) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 7804B68A5B; Tue, 17 Sep 2002 09:46:35 -0500 (CDT) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.34 #1 (Debian)) id 17rJcZ-0001lj-00; Tue, 17 Sep 2002 09:46:35 -0500 Date: Tue, 17 Sep 2002 09:46:35 -0500 From: "Ross J. Reedstrom" To: Bruce Momjian , Roland Roberts Cc: Yudie , pgsql-sql@postgresql.org Subject: Re: How to select and result row number?? Message-ID: <20020917144635.GB6538@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , Bruce Momjian , Roland Roberts , Yudie , pgsql-sql@postgresql.org References: <001301c25b5a$2ef0ea40$8a01a8c0@yudie> <001301c25b5a$2ef0ea40$8a01a8c0@yudie> <200209170312.g8H3C6s16543@candle.pha.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200209170312.g8H3C6s16543@candle.pha.pa.us> User-Agent: Mutt/1.3.27i X-Virus-Scanned: by AMaViS snapshot-20020300 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/193 X-Sequence-Number: 9470 On Mon, Sep 16, 2002 at 11:12:06PM -0400, Bruce Momjian wrote: > Yudie wrote: > 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. > SELECT * FROM out; On Tue, Sep 17, 2002 at 10:14:58AM -0400, Roland Roberts wrote: > 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. Hmm, with the new dependency code, is the auto-sequence from a SERIAL in a temp table also a temp sequence? It get's put in the temp schema, right? Seems we have a workaround for those wanting numbered result sets. BTW, Bruce's solution could be useful for those times you want to batch a potentially large return set, but can't use cursors - the temp table will live as long as the connection does, so as long as your connection pool doesn't play round robin games on you, you can use the result count from the INSERT to calculate batch sizes, and use OFFSET and LIMIT on SELECTs from the temp table. Ross From tgl@sss.pgh.pa.us Tue Sep 17 15:07:21 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AFDB8475E2C for ; Tue, 17 Sep 2002 11:07:21 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 68BE94767D8 for ; Tue, 17 Sep 2002 11:07:19 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g8HF5O5D024104; Tue, 17 Sep 2002 11:05:24 -0400 (EDT) To: "Ross J. Reedstrom" Cc: Bruce Momjian , Roland Roberts , Yudie , pgsql-sql@postgresql.org Subject: Re: How to select and result row number?? In-reply-to: <20020917144635.GB6538@rice.edu> References: <001301c25b5a$2ef0ea40$8a01a8c0@yudie> <001301c25b5a$2ef0ea40$8a01a8c0@yudie> <200209170312.g8H3C6s16543@candle.pha.pa.us> <20020917144635.GB6538@rice.edu> Comments: In-reply-to "Ross J. Reedstrom" message dated "Tue, 17 Sep 2002 09:46:35 -0500" Date: Tue, 17 Sep 2002 11:05:24 -0400 Message-ID: <24103.1032275124@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/194 X-Sequence-Number: 9471 "Ross J. Reedstrom" writes: > Hmm, with the new dependency code, is the auto-sequence from a SERIAL > in a temp table also a temp sequence? It get's put in the temp schema, > right? Seems we have a workaround for those wanting numbered result > sets. That's the hard way; just do CREATE TEMP SEQUENCE ... works in prior releases too ... regards, tom lane From pgman@candle.pha.pa.us Tue Sep 17 21:56:47 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3647F475CA6 for ; Tue, 17 Sep 2002 17:56:47 -0400 (EDT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id 2BE71475AD7 for ; Tue, 17 Sep 2002 17:56:44 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g8HLisd23301; Tue, 17 Sep 2002 17:44:54 -0400 (EDT) From: Bruce Momjian Message-Id: <200209172144.g8HLisd23301@candle.pha.pa.us> Subject: Re: How to select and result row number?? In-Reply-To: <20020917144635.GB6538@rice.edu> To: "Ross J. Reedstrom" Date: Tue, 17 Sep 2002 17:44:53 -0400 (EDT) Cc: Roland Roberts , Yudie , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/197 X-Sequence-Number: 9474 Ross J. Reedstrom wrote: > On Mon, Sep 16, 2002 at 11:12:06PM -0400, Bruce Momjian wrote: > > Yudie wrote: > > > > 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 From tgl@sss.pgh.pa.us Tue Sep 17 22:16:22 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 4D537476780 for ; Tue, 17 Sep 2002 18:16:22 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id B63384767C3 for ; Tue, 17 Sep 2002 18:16:21 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g8HMDG5D008958; Tue, 17 Sep 2002 18:13:16 -0400 (EDT) To: Bruce Momjian Cc: "Ross J. Reedstrom" , Roland Roberts , Yudie , pgsql-sql@postgresql.org Subject: Re: How to select and result row number?? In-reply-to: <200209172144.g8HLisd23301@candle.pha.pa.us> References: <200209172144.g8HLisd23301@candle.pha.pa.us> Comments: In-reply-to Bruce Momjian message dated "Tue, 17 Sep 2002 17:44:53 -0400" Date: Tue, 17 Sep 2002 18:13:16 -0400 Message-ID: <8957.1032300796@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/198 X-Sequence-Number: 9475 Bruce Momjian 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. regards, tom lane From pgman@candle.pha.pa.us Tue Sep 17 22:24:02 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 8D95E4766BF for ; Tue, 17 Sep 2002 18:24:02 -0400 (EDT) Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251]) by postgresql.org (Postfix) with ESMTP id B8A224765FD for ; Tue, 17 Sep 2002 18:23:48 -0400 (EDT) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g8HMLH227467; Tue, 17 Sep 2002 18:21:17 -0400 (EDT) From: Bruce Momjian Message-Id: <200209172221.g8HMLH227467@candle.pha.pa.us> Subject: Re: How to select and result row number?? In-Reply-To: <8957.1032300796@sss.pgh.pa.us> To: Tom Lane Date: Tue, 17 Sep 2002 18:21:17 -0400 (EDT) Cc: "Ross J. Reedstrom" , Roland Roberts , Yudie , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL99 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/199 X-Sequence-Number: 9476 Tom Lane wrote: > Bruce Momjian 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 From tgl@sss.pgh.pa.us Tue Sep 17 22:47:44 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id AA205475C5F for ; Tue, 17 Sep 2002 18:47:44 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 228D4475BF9 for ; Tue, 17 Sep 2002 18:47:44 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g8HMhi5D012284; Tue, 17 Sep 2002 18:43:44 -0400 (EDT) To: Bruce Momjian Cc: "Ross J. Reedstrom" , Roland Roberts , Yudie , pgsql-sql@postgresql.org Subject: Re: How to select and result row number?? In-reply-to: <200209172221.g8HMLH227467@candle.pha.pa.us> References: <200209172221.g8HMLH227467@candle.pha.pa.us> Comments: In-reply-to Bruce Momjian message dated "Tue, 17 Sep 2002 18:21:17 -0400" Date: Tue, 17 Sep 2002 18:43:44 -0400 Message-ID: <12283.1032302624@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/201 X-Sequence-Number: 9478 Bruce Momjian writes: >> Say what? Given the ORDER BY in the subselect, it will. > 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. If you write ORDER BY, we'll implement it. > Does ANSI say it has an ordering? ANSI forbids ORDER BY in sub-selects, so they don't have to address the question. > What if the subquery is involved in a join? We are entitled to reorder its output for purposes of processing the join, then. But that's entirely irrelevant to the example as given. There is no reason to reorder the subselect output, and we won't. regards, tom lane