Hi MonetDB does not support it, although i did not expect a syntax error. http://www.monetdb.org/Documentation/Manuals/SQLreference/Indices regards, Martin On 11/20/13 1:43 PM, Michael Wittig wrote:
Hi,
I am just getting started with monetdb.
I have a table like this:
CREATE TABLE values ( "date" DATE, "datetime" TIMESTAMP, "origin" VARCHAR(30), "value" REAL );
Normally I want to do queries like SELECT date, AVG(value) FROM values WHERE date>='2013-01-01' AND origin='sensor1' GROUP BY date;
date and datetime are sorted so I would assume that an index on origin will be useful. The possible values of origin are limited so I tried:
(Version Feb2013-SP5) CREATE CLUSTERED INDEX index_origin ON values (origin);
But the result is syntax error, unexpected IDENT, expecting INDEX in "create clustered"
What am I missing?
Or is there even a better solution for data that looks like this where origin is sorted within a date.
date | datetime | origin | value 2013-01-01 | 2013-01-01 10:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 12:00:00.000 | sensor1 | 12 2013-01-01 | 2013-01-01 14:00:00.000 | sensor1 | 15 2013-01-01 | 2013-01-01 16:00:00.000 | sensor1 | 10 2013-01-01 | 2013-01-01 10:00:00.000 | sensor2 |111 2013-01-01 | 2013-01-01 12:00:00.000 | sensor2 | 222 2013-01-01 | 2013-01-01 14:00:00.000 | sensor2 | 333 2013-01-01 | 2013-01-01 16:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 10:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 12:00:00.000 | sensor1 | 12 2013-01-02 | 2013-01-02 14:00:00.000 | sensor1 | 15 2013-01-02 | 2013-01-02 16:00:00.000 | sensor1 | 10 2013-01-02 | 2013-01-02 10:00:00.000 | sensor2 | 111 2013-01-02 | 2013-01-02 12:00:00.000 | sensor2 | 222 2013-01-02 | 2013-01-02 14:00:00.000 | sensor2 | 333 2013-01-02 | 2013-01-02 16:00:00.000 | sensor2 | 222
Regards,
Michael _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list