1

I am sure this is the most common problem with Cassandra. Nevertheless:

I have this example table:

CREATE TABLE test.test1 (
a text,
b text,
c timestamp,
id uuid,
d timestamp,
e decimal,
PRIMARY KEY ((a),c, b, id)) WITH CLUSTERING ORDER BY (b ASC, compartment ASC);

My query:

select b, (toUnixTimestamp(d) - toUnixTimestamp(c))/1000/60/60/24/365.25 as age from test.test1  where a = 'x' and c > -2208981600000 ;

This works fine but I can't get the data sorted by column b which I need. I need all the entries in column b and their corresponding 'age's.

eg:

select b, (toUnixTimestamp(d) - toUnixTimestamp(c))/1000/60/60/24/365.25 as age from test.test1  where a = 'x' and c > -2208981600000 order by b;

gives the error:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Order by currently only supports the ordering of columns following their declared order in the PRIMARY KEY"

I have tried different orders in the clustering columns and different options in the partition key but I get caught by some logic and just can't seem to outwit Cassandra to get what I want. If I get the sort order I want, I loose the ability to filter on column 'c'.

Is there some logic I am not applying here, or alternatively, what must I omit(?) to get a list of entries in column b with the corresponding age.

jlb333333
  • 371
  • 2
  • 13

1 Answers1

1

Short answer - it's impossible to sort data on arbitrary column using CQL, even if it's a part of the primary key. Cassandra sorts data first by first clustering column, then inside it by second, etc. (see this answer).

So the only workaround right now is to fetch all data & sort on the client side.

Alex Ott
  • 80,552
  • 8
  • 87
  • 132