Using Order by clause in cassandra

Question

When creating a table in cassandra, we can provide clustering keys in order, as shown 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 this table, according to the documentation documents cassandra are sorted based on the clustering keys.

When I get records with CQL1 and CQL2, I get the same sort 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?

Answer 1

DataStax recommends a query-oriented data modeling approach where results are stored in the order in which they are required at the time of the query. This order is determined by both the partition key and the first clustering column of the primary key [composite]. The DataStax document in the ORDER BY explains this:

Compound primary key queries and sort results In ORDER BY clauses, only one column can be selected. This column must be the second column in the composite primary key. This also applies to tables with more than two column components in the primary key. Sorting can be done in ascending or descending order, ascending by default and is indicated using the ASC or DESC keywords.

In the ORDER BY clause, specify the column using the actual name, not aliases.

For example, set up a playlist table that uses a composite primary key, insert example data and use this query to get information about a specific playlist ordered by song_order. Starting with Cassandra 1.2, there is no need to include the ORDER BY column in the selection expression.

Thus, you can change it only by requesting ASCending or DESCending.

You can also try (free) Java development with the Apache Cassandra online class on DataStax Ac * ademy . I believe session 3 offers a module for the ORDER BY .

Edit 2019

For all future Google, I wrote an article on this subject about a year after this post. Now it is older, but still relevant for streamlining the result set with Cassandra: https://www.datastax.com/dev/blog/we-shall-have-order