How to use OrderBy with primary key in Cassandra CQL?

Question

Following is my Cassandra table structure.

Advertisement

AdvertisementId | Ad_Language | Ad_Caption            | Others
----------------------------------------------------------------------
A01(UUID)       | EN_US (text)| englishCaption (text) | Other Info(text)
A01(UUID)       | FR_CA (text)| French Caption (text) | Other Info (text)

Primary key is (AdvertisementId, Ad_Language);

I am using java to integrate with Cassandra. There is a Java API call to fetch List<advertisements>

Is there a possiblity to fetch the rows like Query : select * from ad_details orderBy advertisementId; (Unfortunately I cannot specify a col_name that will be used in WHERE or In clause)

I cannot have advertisement Id as cluster key as I need to maintain the UUID as partition key of the composite primary key in Cassandra.

The following query works: Select * from ad_details where advertisementId=xxx orderBy language ASC;

Can someone please help me in carrying out the orderBy advertisementId?

Answer 1

You can not order by a partition key when using the MurMur3partitioner or RandomPartitioner. If you are using an ordered partitioner the results will be in the order of the type specified for the partition key when creating the table.

Answer 2

You can't order by primary key unless you are using IN. If you are not limiting your search with "where" you probably need to redesign your table as it is not an efficient design, when table gets big you can't query it in efficient way.