Hi Martin, the page you are sending me was the reason for me to assume that CLUSTERED INDEX works :) Michael Am 20-11-2013 13:46, schrieb Martin Kersten:

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

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Michael Wittig Tullius Walden Bank AG Calwer Str. 11 70173 Stuttgart Phone: +49 711 1377-1371 Fax: +49 711 1377-1399 michael.wittig@tullius-walden.com www.tullius-walden.com Sitz: Stuttgart Registergericht: Stuttgart HRB: 735963 Aufsichtsratsvorsitzender: Hubertus Endres Vorstand: Christoph Metzger, Harry Fix