Bringing together the Apache Cassandra experts from the community and DataStax.

Want to learn? Have a question? Want to share your expertise? You are in the right place!

Not sure where to begin? Getting Started

 

question

gbasky avatar image
gbasky asked ·

Sorting data returns "ORDER BY is only supported when the partition key is restricted by an EQ or an IN"

This is my table

CREATE TABLE videos_by_tag (
    tag text,
    video_id uuid,
    added_date timestamp,
    title text,
    PRIMARY KEY ((tag,added_date),video_id)
) WITH CLUSTERING ORDER BY (added_date desc);

i have been asked to sort by ascending order on column added_date.

when i execute the below query. This is the error i am getting

cqlsh:video> SELECT * FROM videos_by_tag ORDER BY added_date ASC;
InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or an IN."
cql
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

Erick Ramirez avatar image
Erick Ramirez answered ·

I'm assuming you made an error with the table schema you posted:

    PRIMARY KEY ((tag,added_date),video_id)

because if the partition key is (tag,added_date), you wouldn't be able to define the clustering order for the added_date column since it would't be a valid schema.

I believe the correct definition is:

    PRIMARY KEY (tag,added_date,video_id)

As it states in the error message you posted, the reason you can't change the order on the added_date column is because you didn't specify (restrict) the query to a partition key:

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

You have an unbounded (unrestricted) query meaning that you are retrieving all the partitions in the table. Cassandra cannot sort all the rows in all the partitions to satisfy your request.

You need to filter your query to a specific partition so Cassandra can sort the rows within that partition. Cheers!

Share
10 |1000 characters needed characters left characters exceeded

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.