Received: from localhost.localdomain (postgresql.org [64.49.215.8]) by localhost (Postfix) with ESMTP id 1A455475481 for ; Sun, 5 May 2002 01:40:53 -0400 (EDT) Received: from mps3.plala.or.jp (mpsb-nat18.plala.or.jp [202.212.115.65]) by postgresql.org (Postfix) with ESMTP id 94708474E42 for ; Sun, 5 May 2002 01:40:51 -0400 (EDT) Received: from VIP333 ([218.47.117.95]) by mps3.plala.or.jp with ESMTP id <20020505054050.OXLX15489.mps3.plala.or.jp@VIP333>; Sun, 5 May 2002 14:40:50 +0900 Date: Sun, 05 May 2002 14:43:16 +0900 From: Masaru Sugawara To: Jakub Ouhrabka Subject: Re: difficult query Cc: pgsql-sql@postgresql.org In-Reply-To: References: <200205041744.48054.barwick@gmx.net> Errors-To: rk73@sea.plala.or.jp Message-Id: <20020505061938.1E8A.RK73@sea.plala.or.jp> MIME-Version: 1.0 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit X-Mailer: Becky! ver. 2.00.08 X-Archive-Number: 200205/33 X-Sequence-Number: 7669 On Sat, 4 May 2002 19:34:03 +0200 (CEST) Jakub Ouhrabka wrote: > > Basically, no. (You might be able to do it with the help > > of a user-defined function though). I also think so. > yesterday when I faced this problem (which can be solved perfectly with > the outer join) I wasn't able to remember the solution... If there is > any... Just out of curiosity... I wouldn't think that that can be always solved. Whether it can be or not depends on the elements of "day" column. But until its aggregation will lack two successive numbers or more, the following query is useful, I guess. CREATE VIEW v_table1 AS SELECT tt.day , COUNT(tt.data) AS data FROM (SELECT t.day, t.data FROM table1 AS t UNION ALL SELECT 1, NULL -- indispensable if there's no row of day=1. )AS tt GROUP BY tt.day ; SELECT t0.day, t0.data FROM v_table1 AS t0 UNION SELECT t1.day - 1 , 0 FROM v_table1 AS t1, v_table1 AS t2 WHERE t1.day > t2.day GROUP BY t1.day HAVING t1.day > MAX(t2.day) + 1 ; In addition, until its aggregation will lack THREE ones or more, ... SELECT t0.day, t0.data FROM v_table1 AS t0 UNION SELECT t1.day - 1, 0 FROM ... UNION SELECT t3.day - 2, 0 FROM ... Regards, Masaru Sugawara