Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2KHiQN95134 for ; Tue, 20 Mar 2001 12:44:26 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [207.172.28.154] (HELO agliodbs.com) by davinci.ethosmedia.com (CommuniGate Pro SMTP 3.4.1) with ESMTP id 27206; Tue, 20 Mar 2001 09:44:25 -0800 Message-ID: <3AB796B8.AD87AE95@agliodbs.com> Date: Tue, 20 Mar 2001 09:43:20 -0800 From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions X-Mailer: Mozilla 4.73 [en] (Win98; I) X-Accept-Language: en MIME-Version: 1.0 To: Rachel Coin CC: pgsql-sql@postgresql.org Subject: Re: Categories and subcategories : more details References: <00c901c0b15e$5b7dff00$6101a8c0@spidinfo.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/391 X-Sequence-Number: 2059 Rachel, See below: > Examples : > > table CATEG : > ida categ > 1 X > 2 Y > 3 Z > > table SUBCATEG : > idb subcateg ida > 1 x1 1 > 2 x2 1 > 3 x3 1 > 4 x4 1 > 5 y1 2 > 6 y2 2 > 7 z1 3 > > I 'd like to obtain a table with maximum 2 subcateg per categ : > > table result : > > categ subcateg > X x1 > X x2 > Y y1 > Y y2 > Z z1 > Without LIMIT in subselects, you're in performance hell as far as doing the above is concerned. Frankly, I can't see any way to do it that doesn't involve a PL/pgSQL procedure that generates a temporary table, and has to run once for every row in your result set. I'll post code later if nobody comes up with a better solution. If you knew all of your categories in advance, you could do this via an annoying UNION statement. I'm assuming, however, that it needs to be dynamic. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco