On Wed, Jun 01, 2011 at 08:54:19PM +0200, Fabian Groffen wrote:
On 01-06-2011 11:48:17 -0700, Synaptic Axon wrote:
Unfortunately, MonetDB does not appear to support positional fields in the GROUP BY clause. So I tried rewriting it like this:
sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY EXTRACT( year from ts ), category ORDER BY 3 DESC; syntax error, unexpected EXTRACT in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY EXTRACT( year from ts ), category ORDER BY 3 DESC"
Ok, how about using "year" instead of the extract( .. ) bit?
sql> SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY year, category ORDER BY 3 DESC; syntax error, unexpected YEAR in: "SELECT EXTRACT( year from ts ) as year, category, COUNT(category) FROM foo GROUP BY year, category ORDER BY 3 DESC"
What is the correct way to group by the computed field? Should I create a view? Is there a positional syntax for the GROUP BY clause I am overlooking?
Niels knows exactly why this isn't allowed, but why do see this as an issue? I agree it clobbers the query, but that should be all...
Indeed we don't allow functions within the group by list (only column or alias references). Shouldn't be a big problem. In this case it goes wrong because 'year' is a keyword which needs double quotes if you want to use it as alias/ident. Niels
------------------------------------------------------------------------------ Simplify data backup and recovery for your virtual environment with vRanger. Installation's a snap, and flexible recovery options mean your data is safe, secure and there when you need it. Data protection magic? Nope - It's vRanger. Get your free trial download today. http://p.sf.net/sfu/quest-sfdev2dev _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl