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

select cassandra sql-order-by cql
fiticida picture fiticida · Oct 25, 2017 · Viewed 14.1k times · Source

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

pconley picture pconley · Oct 25, 2017

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

Related questions

Finding distinct values of non Primary Key column in CQL Cassandra

I use the following code for creating table: CREATE KEYSPACE mykeyspace WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor' : 1 }; USE mykeyspace; CREATE TABLE users ( user_id int PRIMARY KEY, fname text, lname text ); INSERT INTO users (user_id, fname, lname) VALUES (1745, 'john', …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
Selecting multiple columns in a pandas dataframe

I have data in different columns but I don't know how to extract it to save it in another variable. index a b c 1 2 3 4 2 3 4 5 How do I select 'a', 'b' and save it in to df1? I tried df1 = df[…

Read More