Order by created date In Cassandra

Question

i have problem with ordering data in cassandra Database. this is my table structure:

CREATE TABLE posts (
    id uuid,
    created_at timestamp,
    comment_enabled boolean,
    content text,
    enabled boolean,
    meta map<text, text>,
    post_type tinyint,
    summary text,
    title text,
    updated_at timestamp,
    url text,
    user_id uuid,
    PRIMARY KEY (id, created_at)
) WITH CLUSTERING ORDER BY (created_at DESC)

and when i run this query, i got the following message:

Query:

 select * from posts order by created_at desc;

message:

ORDER BY is only supported when the partition key is restricted by an EQ or an IN.

Or this query return data without sorting:

select * from posts

Answer 1

There are couple of things you need to understand, In your case the partition key is "id" and the clustering key is "created_at".

what that essentially means is any row will be stored in a partition based on the hash of "id"(depending on your hashing scheme by default it is Murmur3), now inside that partition the data is sorted based on your clustering key, in your case "created_at".

So if you query some data from that table by default the results which come are sorted based on your clustering order and the default sort order is the one which you specify while creating the table. However there is a gotcha there.

If yo do not specify the partition key in the WHERE clause, the actual order of the result set then becomes dependent on the hashed values of partition key(in your case id).

So in order to get the posts by that specific order. you have to specify the partition key like this

select * from posts WHERE id=1 order by created_at desc;

Note:

It is not necessary to specify the ORDER BY clause on a query if your desired sort direction (“ASCending/DESCending”) already matches the CLUSTERING ORDER in the table definition.

So essentially the above query is same as

select * from posts WHERE id=1

You can read more about this here http://www.datastax.com/dev/blog/we-shall-have-order

Answer 2

The error message is pretty clear: you cannot ORDER BY without restricting the query with a WHERE clause. This is by design.

The data you get when running without a WHERE clause actually are ordered, not with your clustering key, but by applying the token function to your partition key. You can verify the order by issuing:

SELECT token(id), id, created_at, user_id FROM posts;

where the token function arguments exactly match your PARTITION KEY.

I suggest you to read this and this to understand what you can/can't do.