Re: GROUPing by expressions, and subSELECTs

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 <me@shanewright.co.uk> 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