CODEWITHSUNDEEP
cassandra
J Del
J Del

Reputation: 881

Cassandra Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY

This is the query I used to create the table:

CREATE TABLE test.comments (msguuid timeuuid, page text, userid text, username text, msg text, timestamp int, PRIMARY KEY (timestamp, msguuid));

then I create a materialized view:

CREATE MATERIALIZED VIEW test.comments_by_page AS
    SELECT *
    FROM test.comments
    WHERE page IS NOT NULL AND msguuid IS NOT NULL
    PRIMARY KEY (page, timestamp, msguuid)
    WITH CLUSTERING ORDER BY (msguuid DESC);

I want to get the last 50 rows sorted by timestamp in ascending order.

This is the query I'm trying:

SELECT * FROM test.comments_by_page WHERE page = 'test' AND timestamp < 1496707057 ORDER BY timestamp ASC LIMIT 50;

which then gives this error: InvalidRequest: code=2200 [Invalid query] message="Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY"

How can I accomplish this?

Upvotes: 3

Views: 6388

Answers (2)

user6882413
user6882413

Reputation: 341

clustering order statement should be modified as below: //Don't forget to put the primary key before timestamp into ()

CLUSTERING ORDER BY ((msguuid DESC), timestamp ASC)

Upvotes: -1

xmas79
xmas79

Reputation: 5180

Materialized View rules are basically the same of "standard" tables ones. If you want a specific order you must specify that in the clustering key.

So you have to put your timestamp into the clustering section.

Upvotes: 1

Related Questions