Hi
Is it possible to restrict select distinct by adding something in where
part?
For example:
cqlsh> select distinct weatherstation_id, date from mixer.temperature;
weatherstation_id | date
-------------------+------------
1234ABCD | 2013-04-05
1234ABCD | 2013-04-04
1234ABCD | 2013-04-03
(3 rows)
cqlsh> select distinct weatherstation_id, date from mixer.temperature
where event_time>'2013-04-06 00:00' and event_time<'2013-04-09 00:00' limit
10000 allow filtering;
weatherstation_id | date
-------------------+------------
1234ABCD | 2013-04-05
1234ABCD | 2013-04-04
1234ABCD | 2013-04-03
(3 rows)
When I add:
where event_time>'2013-04-06 00:00' and event_time<'2013-04-09 00:00'
to query it doesn't seem to have any effect?
I am using Cassandra 2.0.4
cqlsh> desc table mixer.temperature;
CREATE TABLE temperature (
weatherstation_id text,
date text,
event_time timestamp,
temperature text,
PRIMARY KEY ((weatherstation_id, date), event_time)
) WITH
bloom_filter_fp_chance=0.010000 AND
caching='KEYS_ONLY' AND
comment='' AND
dclocal_read_repair_chance=0.000000 AND
gc_grace_seconds=864000 AND
index_interval=128 AND
read_repair_chance=0.100000 AND
replicate_on_write='true' AND
populate_io_cache_on_flush='false' AND
default_time_to_live=0 AND
speculative_retry='99.0PERCENTILE' AND
memtable_flush_period_in_ms=0 AND
compaction={'class': 'SizeTieredCompactionStrategy'} AND
compression={'sstable_compression': 'LZ4Compressor'};
CREATE INDEX temp_event_time ON temperature (event_time);
cqlsh>
cqlsh> SELECT * FROM mixer.temperature;
weatherstation_id | date | event_time | temperature
-------------------+------------+--------------------------+-------------
1234ABCD | 2013-04-05 | 2013-04-05 07:00:34+0300 | 37F
1234ABCD | 2013-04-05 | 2013-04-05 07:02:00+0300 | 17F
1234ABCD | 2013-04-05 | 2013-04-05 07:08:00+0300 | 37F
1234ABCD | 2013-04-04 | 2013-04-04 07:00:03+0300 | 67F
1234ABCD | 2013-04-04 | 2013-04-04 07:00:05+0300 | 77F
1234ABCD | 2013-04-04 | 2013-04-04 07:00:50+0300 | 24F
1234ABCD | 2013-04-04 | 2013-04-04 07:01:00+0300 | 73F
1234ABCD | 2013-04-04 | 2013-04-04 07:02:00+0300 | 74F
1234ABCD | 2013-04-03 | 2013-04-03 07:01:00+0300 | 72F
1234ABCD | 2013-04-03 | 2013-04-03 07:02:00+0300 | 73F
(10 rows)
Thanks
toivo
