question

gbasky avatar image
gbasky asked Erick Ramirez commented

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
Toggle Comment visibility. Current Visibility: Viewable by all users

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 Erick Ramirez commented

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!

2 comments Share
10 |1000
Toggle Comment visibility. Current Visibility: Viewable by all users

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

pennkaiser avatar image pennkaiser commented ·

Just tried this but does not work

Nov 01 2021 Created table as suggested - still have with clustering order by (added_date)

but when I type SELECT * FROM videos_by_tag WHERE added_date > '2012-01-01' ORDER BY added_date ASC

I get

InvalidRequest: Error from server code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or IN."
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) ;
0 Likes 0 ·
Erick Ramirez avatar image Erick Ramirez ♦♦ pennkaiser commented ·

I get the sense that you didn't read my answer. :)

If you do not filter (restrict) on a partition key, you cannot sort the results. Filtering on a partition key means having pk = ? in the WHERE clause:

SELECT ... FROM ... WHERE pk = ? ...
0 Likes 0 ·