You're going to have a problem doing this in a single query because you're asking cassandra to select a non-contiguous set of rows. Also, to my knowledge, you can only use non equal operators on clustering keys. The best solution I could come up with would be to define you table like so:
CREATE TABLE room_data ( room_id uuid, in_room int, temp float, time timestamp, PRIMARY KEY (room_id, in_room, temp)); Then run 2 queries: SELECT * FROM room_data WHERE in_room > 7; SELECT * FROM room_data WHERE temp > 50.0; And do an intersection on the results. I should add the disclaimer that I am relatively new to CQL, so there may be a better way to do this. Blake On Wed, Nov 28, 2012 at 10:02 AM, Oren Karmi <oka...@gmail.com> wrote: > Hi, > > According to the documentation on Indexes ( > http://www.datastax.com/docs/1.1/ddl/indexes ), > in order to use WHERE on a column which is not part of my key, I must > define a secondary index on it. However, I can only use equality comparison > on it but I wish to use other comparisons methods like greater than. > > Let's say I have a room with people and every timestamp, I measure > the temperature of the room and number of people. I use the timestamp as my > key and I want to select all timestamps where temperature was over 50 > degrees but I can't seem to be able to do it with a regular query even if I > define that column as a secondary index. > SELECT * FROM MyTable WHERE temp > 50.4571; > > My lame workaround is to define a secondary index on NumOfPeopleInRoom and > than for a specific value > SELECT * FROM MyTable WHERE NumOfPeopleInRoom = 7 AND temp > 50.4571; > > I'm pretty sure this is not the proper way for me to do this. > > How should I attack this? It feels like I'm missing a very basic concept. > I'd appreciate it if your answers include also the option of not changing > my schema. > > Thanks!!! >
