[GENERAL] xpath_string and group by

[Perez]

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

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] xpath_string and group by

[Martijn van Oosterhout]

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.


signature.asc
Description: Digital signature

Re: [GENERAL] xpath_string and group by

[Arturo Perez]

In article [EMAIL PROTECTED],
 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

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] xpath_string and group by

[Martijn van Oosterhout]

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.


signature.asc
Description: Digital signature