When creating table in cassandra, we can give clustering keys with ordering like below.
Create table user(partitionkey int, id int, name varchar, age int, address text,
insrt_ts timestamp,
Primary key(partitionkey, name, insrt_ts, id)
with clustering order by (name asc, insrt_ts desc, id asc);
when we insert data into that table, As per cassandra documentation records are sorted based on clustering keys.
When i retrieve records with CQL1 and CQL2, I am getting in the same sorted order.
CQL1:
Select * from user where partitionkey=101;
CQL2:
Select * from user where partitionkey=101 order by name, insrt desc, id;
what is the difference between CQL1 and CQL2?
Best Answer
DataStax recommends a query-oriented data modeling approach, where the results are stored in the order in which they are required at query-time. This order is determined by both the partition key and first clustering column of a [compound] primary key. The DataStax doc on the
ORDER BY
clause explains this:So basically you can really only alter it by asking for ASCending or DESCending order.
You also might want to check out the (free) Java development with Apache Cassandra online class at DataStax Ac*ademy. I believe session 3 offers a module on the
ORDER BY
clause.Edit 2019
For all future Googlers, I wrote an article on this topic about a year after this post. It's older now, but still relevant to result set ordering with Cassandra: https://www.datastax.com/dev/blog/we-shall-have-order