Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 4981A2E0047 for ; Wed, 9 Apr 2008 00:34:36 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 01614-03 for ; Wed, 9 Apr 2008 00:34:23 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from hs-out-0708.google.com (hs-out-0708.google.com [64.233.178.241]) by developer.postgresql.org (Postfix) with ESMTP id 4E8612E004A for ; Wed, 9 Apr 2008 00:34:23 -0300 (ADT) Received: by hs-out-0708.google.com with SMTP id x43so9130837hsb.3 for ; Tue, 08 Apr 2008 20:34:23 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=9K0hGUhNH4QmjHbdrgUQmD7TD4ykk5HuJ+CLy1/OScw=; b=Ly+snAiOVdVzqkgLJ9oaBEJomSpjMguM83Gmuz6ZLg3UJAb0ABwQMzuDMlfwtmKqxWaG/8UyCoEqiwsYJxb6t/N6NhuQnFe6ZHiy8PoSkRalfLuG96kf2J7eA9jJbAwWYS7fk6oPhyxsSApDVCV19MXcnqHOm7Li9ntYV4ppSzQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Z3Uin6pXNay8QOl4JCC6zJsjBW3rr6lccK3PdNFCODnhQxcDVp979ELkcVUw9be3f6rNkZ5c9FWDMkQ3bBWADoRuxofoQC3gjhrOQRCtzOpIrY7c9mE1jaPkt2Lcg3whEwbjn+IhG48ow+tJes7IHWSar5kRLI3eK5lw/G15jR0= Received: by 10.100.138.16 with SMTP id l16mr1364558and.144.1207712062902; Tue, 08 Apr 2008 20:34:22 -0700 (PDT) Received: by 10.100.122.9 with HTTP; Tue, 8 Apr 2008 20:34:22 -0700 (PDT) Message-ID: Date: Tue, 8 Apr 2008 22:34:22 -0500 From: rdeleonp@gmail.com To: novice Subject: Re: count question Cc: pgsql-sql@postgresql.org In-Reply-To: <47FC25DB.2090803@postnewspapers.com.au> MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <47FC25DB.2090803@postnewspapers.com.au> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/56 X-Sequence-Number: 30595 On 4/8/08, Craig Ringer wrote: > novice wrote: > > > Is it possible to write a query to produce: > > > > meter_id | no_of_bays | bay_id > > ----------+------------+----------- > > 5397 | 2 | 5397-01 > > 5397 | 2 | 5397-02 > > 5409 | 3 | 5409-01 > > 5409 | 3 | 5409-02 > > 5409 | 3 | 5409-03 > > Sure. One way, not necessarily a particularly clever or efficient way, > is to do a join with: > > generate_series(1, (select max(no_of_bays) from meter)) as i > > and use a WHERE clause to select for `i <= no_of_bays' You have a problem, and it's bad design. A bad design leads to these types of kludges. Consider re-engineering your schema. In any case, good luck.