Cassandra error - Order By only supported when partition key is restricted by EQ or IN

Question

Here is the table I'm creating, this table contains information about players that played the last mundial cup.

CREATE TABLE players ( 
      group text, equipt text, number int, position text, name text,
      day int, month int, year int, 
      club text, liga text, capitan text,
PRIMARY key (name, day, month, year));

When doing the following query :

Obtain 5 names from the oldest players that were captain of the selection team

Here is my query:

SELECT name FROM players WHERE captain='YES' ORDER BY year DESC LIMIT 5;

And I am getting this error:

Order By only supported when partition key is restricted by EQ or IN

I think is a problem about the table I'm creating, but I don't know how to solve it.

Thanks.

Answer 1

Your table definition is incorrect for the query you're trying to run.

You've defined a table with partition key "name", clustering columns "day", "month", "year", and various other columns.

In Cassandra all SELECT queries must specify a partition key with EQ or IN. You're permitted to include some or all of the clustering columns, using the equality and inequality operators you're used to in SQL.

The clustering columns must be included in the order they're defined. An ORDER BY clause can only include clustering columns that aren't already specific by an EQ, again in the order they're defined.

For example, you can write the query

select * from players where name = 'fiticida' and day < 5 order by month desc;

or

select * from players where name = 'fiticida' and day = 10 and month > 2 order by month asc;

but not

select * from players where name = 'fiticida' and year = 2017;

which doesn't include "day" or "month"

and not

select * from players where name = 'fiticida' and day = 5 order by year desc;

which doesn't include "month".

Here is the official documentation on the SELECT query.

---

To satisfy your query, the table needs

• A partition key specified by EQ or IN: "captain" will work
• An ORDER BY clause using the leftmost clustering column: put "year" to the left of "month" and "day" in your primary key definition