Postgres Pro
Downloads
Home   >   mailing lists

Thread: xpath_string and group by

xpath_string and group by

From
Perez
Date:
Hi all,

Using 8.1.4 and contrib/xml2.  When I do a

select xpath_string(note, '//Thing') as note,
   count(aDate) from theTable
group by lower(xpath_string(note, '//Thing'))
order by 2 desc;

I get an error:
GROUP BY must contain note.
But I can do that for a plain text/varchar field.  Adding the non-xpath
note field messes up the grouping.

Can someone explain what interaction of features causes the above?  I'm
finding that to do the counts the way I want (case-insensitive with
trimmed blanks) I end up doing some variation of:

select note, count(aDate) from
  (select  lower(xpath_string(note, '//Thing')) as note, aDate from
theTable) as foo
group by note

tia,
arturo

Re: xpath_string and group by

From
Martijn van Oosterhout
Date:
On Sat, Aug 26, 2006 at 03:51:06PM -0400, Perez wrote:
> Hi all,
>
> Using 8.1.4 and contrib/xml2.  When I do a
>
> select xpath_string(note, '//Thing') as note,
>    count(aDate) from theTable
> group by lower(xpath_string(note, '//Thing'))
> order by 2 desc;
>
> I get an error:
> GROUP BY must contain note.
> But I can do that for a plain text/varchar field.  Adding the non-xpath
> note field messes up the grouping.

I wonder if it's getting confused about which "note" you're referring
to in the GROUP BY clause.

> select note, count(aDate) from
>   (select  lower(xpath_string(note, '//Thing')) as note, aDate from
> theTable) as foo
> group by note

This is about the same thing, so why not use that?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment

Re: xpath_string and group by

From
Arturo Perez
Date:
In article <20060829105458.GB22806@svana.org>,
 kleptog@svana.org (Martijn van Oosterhout) wrote:

> On Sat, Aug 26, 2006 at 03:51:06PM -0400, Perez wrote:
> > Hi all,
> >
> > Using 8.1.4 and contrib/xml2.  When I do a
> >
> > select xpath_string(note, '//Thing') as note,
> >    count(aDate) from theTable
> > group by lower(xpath_string(note, '//Thing'))
> > order by 2 desc;
> >
> > I get an error:
> > GROUP BY must contain note.
> > But I can do that for a plain text/varchar field.  Adding the non-xpath
> > note field messes up the grouping.
>
> I wonder if it's getting confused about which "note" you're referring
> to in the GROUP BY clause.
>
> > select note, count(aDate) from
> >   (select  lower(xpath_string(note, '//Thing')) as note, aDate from
> > theTable) as foo
> > group by note
>
> This is about the same thing, so why not use that?
>
> Have a nice day,

You're right, it is almost the same thing.  But the second form loses
the case of the original - everything is returned lower case.  If I must
I must but I'ld like to preserve the case is possible.

-arturo

Re: xpath_string and group by

From
Martijn van Oosterhout
Date:
On Tue, Aug 29, 2006 at 07:05:44AM -0400, Arturo Perez wrote:
> > > select note, count(aDate) from
> > >   (select  lower(xpath_string(note, '//Thing')) as note, aDate from
> > > theTable) as foo
> > > group by note
> >
> > This is about the same thing, so why not use that?
>
> You're right, it is almost the same thing.  But the second form loses
> the case of the original - everything is returned lower case.  If I must
> I must but I'ld like to preserve the case is possible.

Oh I see, you want to group thing ignoring case, but want to keep the
case and presumably you don't care which version you get.

Well, how about:

select max(note), count(aDate) from
  (select  xpath_string(note, '//Thing') as note, aDate from  theTable) as foo
group by lower(note);

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment