CODEWITHSUNDEEP
pythonselectcassandranosql
Gokul
Gokul

Reputation: 1

Cassandra 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."

I want to sort my data based on item_session. Tried using the below codes but I am not able to get a sorted output

CREATE TABLE IF NOT EXISTS music1 (
  user text, 
  artist text, 
  song_title text, 
  session_no,
  item_session text, 
  PRIMARY KEY ((user,session_no),item_session)
) WITH CLUSTERING ORDER BY (item_session ASC);

Tried using order by but got an error stating Cassandra 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."

select * from music1 where user = '15' AND session_no = '200' 
Order by item_session desc ALLOW FILTERING

Please let me know where I am going wrong on this

Upvotes: 0

Views: 2798

Answers (1)

Erick Ramirez
Erick Ramirez

Reputation: 16343

The query you posted is actually valid. But the use of ALLOW FILTERING is in fact unnecessary and will work without it:

SELECT * FROM music1
  WHERE user = '15'
  AND session_no = '200' 
  ORDER BY item_session DESC;

For you to get the error, the more likely scenario is that you ran:

SELECT * FROM music1 
  ORDER BY item_session DESC;

to 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 an IN."

As it states in the error message, it isn't possible to change the order of the rows without specifying (restricting) the query to a partition key.

In an unbounded (unrestricted) query, Cassandra has to retrieve all the partitions in the table. Cassandra cannot sort all the rows in all the partitions to satisfy the request.

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

Upvotes: 1

Related Questions