Cassandra error: "Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive"

Question

I am running a cassandra query, actually previously done this. but now i can't execute the query, it throws error:

    Cassandra error: InvalidRequest: Error from server: code=2200 [Invalid query] message="Clustering key columns must exactly match columns in CLUSTERING ORDER BY directive"

My query is:

    CREATE TABLE statistics(country_name text, dt date, confirmed_cases bigint, deaths bigint, 
    PRIMARY KEY(deaths))with clustering order by (deaths DESC);

Please Help!

Answer 1

This is happening because you have only specified a single PRIMARY KEY. Single PRIMARY KEYs default to become partition keys. So two problems here:

• You need to define a clustering key.
• You're trying to enforce order by your partition key.

There are a couple of options here. But as you want to order by deaths, you probably should specify a different column as your partition key. Maybe partition by country_name?

...
PRIMARY KEY (country_name,deaths))
WITH CLUSTERING ORDER BY (deaths DESC);

The caveat, is then you would need to also/always filter by country_name in your WHERE clause.