From dmh@airdefense.net Thu Aug 8 20:11:27 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7D49247546C for ; Thu, 8 Aug 2002 16:11:27 -0400 (EDT) Received: from smaug.securitymatrix.com (unknown [64.238.98.87]) by postgresql.org (Postfix) with ESMTP id A84E2475452 for ; Thu, 8 Aug 2002 16:11:26 -0400 (EDT) Received: from kaos.securitymatrix.com (kaos.securitymatrix.com [172.16.0.101]) by smaug.securitymatrix.com (8.11.6/8.11.6) with ESMTP id g78KDEW02491 for ; Thu, 8 Aug 2002 16:13:29 -0400 Subject: Table Sorting and Limit Question From: Dawn Hollingsworth To: pgsql-sql@postgresql.org Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 08 Aug 2002 16:10:38 -0400 Message-Id: <1028837453.4754.1.camel@kaos> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/156 X-Sequence-Number: 9002 Currently we have a table with a sequence number( id ) as a primary key, a date field which is indexed and several other columns. The user interface allows the user to sort the data by date and limits the result set to 100 rows. The question is: The user interface needs the capability to sort the table by date but pull out the hundred row set that contains id say...542 for example. What would be the best way to do this taking into account this table is several hundred thousand rows? Dawn Hollingsworth Principal Engineer AirDefense, Inc. From lud_nowhere_man@yahoo.com Fri Aug 9 01:42:19 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id F3AE94754A4 for ; Thu, 8 Aug 2002 21:42:19 -0400 (EDT) Received: from web40009.mail.yahoo.com (web40009.mail.yahoo.com [66.218.78.27]) by postgresql.org (Postfix) with SMTP id 4A43D47546C for ; Thu, 8 Aug 2002 21:42:19 -0400 (EDT) Message-ID: <20020809014223.3100.qmail@web40009.mail.yahoo.com> Received: from [202.163.208.14] by web40009.mail.yahoo.com via HTTP; Thu, 08 Aug 2002 18:42:23 PDT Date: Thu, 8 Aug 2002 18:42:23 -0700 (PDT) From: Ludwig Lim Subject: Re: Table Sorting and Limit Question To: Dawn Hollingsworth Cc: PosgreSQL Mailing List In-Reply-To: <1028837453.4754.1.camel@kaos> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/157 X-Sequence-Number: 9003 --- Dawn Hollingsworth wrote: > > > Currently we have a table with a sequence number( id > ) as a primary key, > a date field which is indexed and several other > columns. The user > interface allows the user to sort the data by date > and limits the result > set to 100 rows. > > The question is: > The user interface needs the capability to sort the > table by date but > pull out the hundred row set that contains id > say...542 for example. > > What would be the best way to do this taking into > account this table is > several hundred thousand rows? try also to index the id. Try : Select * from where id= order by date limit In this case you is 100 since you want to return at most 100 rows. hope that helps. ludwig. __________________________________________________ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com From dmh@airdefense.net Fri Aug 9 13:56:05 2002 Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A206047549E for ; Fri, 9 Aug 2002 09:56:05 -0400 (EDT) Received: from smaug.securitymatrix.com (unknown [64.238.98.87]) by postgresql.org (Postfix) with ESMTP id D669447546C for ; Fri, 9 Aug 2002 09:56:04 -0400 (EDT) Received: from kaos.securitymatrix.com (kaos.securitymatrix.com [172.16.0.101]) by smaug.securitymatrix.com (8.11.6/8.11.6) with ESMTP id g79DvvW04441; Fri, 9 Aug 2002 09:58:07 -0400 Subject: Re: Table Sorting and Limit Question From: Dawn Hollingsworth To: Ludwig Lim Cc: PosgreSQL Mailing List In-Reply-To: <20020809014223.3100.qmail@web40009.mail.yahoo.com> References: <20020809014223.3100.qmail@web40009.mail.yahoo.com> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 09 Aug 2002 09:55:24 -0400 Message-Id: <1028901334.1423.18.camel@kaos> Mime-Version: 1.0 X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/163 X-Sequence-Number: 9009 Since the id is a sequence type it should be unique. In this case it also happens to be my primary key into this table which will also make it unique. So the query below would only return 1 row. The part I'm missing is the offset number of the limit. Is there any way to figure out which offset into the query would contain the id I'm interested in. Dawn Hollingsworth Principal Engineer AirDefense, Inc. On Thu, 2002-08-08 at 21:42, Ludwig Lim wrote: > > --- Dawn Hollingsworth wrote: > > > > > > Currently we have a table with a sequence number( id > > ) as a primary key, > > a date field which is indexed and several other > > columns. The user > > interface allows the user to sort the data by date > > and limits the result > > set to 100 rows. > > > > The question is: > > The user interface needs the capability to sort the > > table by date but > > pull out the hundred row set that contains id > > say...542 for example. > > > > What would be the best way to do this taking into > > account this table is > > several hundred thousand rows? > > try also to index the id. > > Try : > Select * > from
> where id= > order by date > limit > > In this case you is 100 since you want to > return at most 100 rows. > > hope that helps. > > ludwig. > > > __________________________________________________ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org