CODEWITHSUNDEEP
cassandrasql-order-by
Bouki
Bouki

Reputation: 1359

cassandra queries with order by

I have a table in cassandra and I need only these 2 queries :

SELECT * FROM comment WHERE post_id = ? ORDER BY date_posted DESC;
SELECT date_posted FROM comment WHERE post_id = ? ORDER BY date_posted DESC LIMIT 1;

My table is :

CREATE TABLE comment
(
    id text,
    post_id text,
    username text,
    comment text,
    date_posted timestamp,
    PRIMARY KEY ((id), date_posted)
) WITH CLUSTERING ORDER BY (date_posted DESC);
CREATE INDEX comment_post_id ON comment (post_id);

I don't understand why it's not working, the order by is not working

Upvotes: 0

Views: 446

Answers (2)

undefined_variable
undefined_variable

Reputation: 6218

You are querying based on secondary index and order by clause is not supported along with secondary index. And if those are the only 2 queries you will be doing on comment table then post_id as partition key will be much more efficient.

Upvotes: 0

Albin Mathew
Albin Mathew

Reputation: 604

you can’t do No arbitrary ORDER BY clauses in CQL. The CQL WHERE clause only works for indexed columns, specifically columns in the primary key (both the partition key and the clustering columns), and columns that have secondary indexes on them. In case of secondary indexes query took data from multiple partitions, so it will not come as a sorted order. That is the reason Cassandra not allowing OrderBy in the above case.

You can refer this link for further details

Breaking Down the CQL Where Clause

Upvotes: 2

Related Questions