Received: from smtp-relay.sb.dsvr.net (sb1.ho.dsvr.net [212.69.222.8]) by postgresql.org (Postfix) with ESMTP id 234BE474E6D for ; Fri, 1 Mar 2002 07:18:39 -0500 (EST) Received: from [212.69.206.2] (helo=fullerruss.dsvr.co.uk) by smtp-relay.sb.dsvr.net with esmtp (Exim 3.11 #2) id 16glzU-00070A-00; Fri, 01 Mar 2002 12:18:24 +0000 Received: from there (host213-122-48-124.in-addr.btopenworld.com [213.122.48.124]) by fullerruss.dsvr.co.uk (8.11.3/8.11.1) with SMTP id g21CIFi18470; Fri, 1 Mar 2002 12:18:15 GMT Message-Id: <200203011218.g21CIFi18470@fullerruss.dsvr.co.uk> Content-Type: text/plain; charset="iso-8859-1" From: Shane Wright Reply-To: me@shanewright.co.uk To: pgsql-sql@postgresql.org Subject: Re: GROUPing by expressions, and subSELECTs Date: Fri, 1 Mar 2002 12:18:13 +0000 X-Mailer: KMail [version 1.3.2] References: <200202281830.g1SIU1s11290@fullerruss.dsvr.co.uk> <14958.1014932420@sss.pgh.pa.us> In-Reply-To: <14958.1014932420@sss.pgh.pa.us> Cc: Tom Lane MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/1 X-Sequence-Number: 6883 Tom, you're a star! It took me a minute to work out what you meant, but hte whole thing is working a treat now! Thanks again, Shane On Thursday 28 Feb 2002 9:40 pm, Tom Lane wrote: > Shane Wright writes: > > SELECT > > [fields], > > [expression], > > (SELECT * FROM table2 WHERE [expression]) AS mynewfield > > FROM > > table > > GROUP BY > > [expression] > > > > But, it gives this error: > > > > ERROR: Sub-SELECT uses un-GROUPed attribute [table].[field] from outer > > query > > Yeah, the code is not bright enough to figure out that this could be > considered legitimate. But try this: > > SELECT > [fields], > myexpr, > (SELECT * FROM table2 WHERE ss.myexpr) AS mynewfield > FROM > (SELECT [fields], > [expression] as myexpr > FROM table > GROUP BY myexpr) AS ss > > regards, tom lane