Clustering order does not work with compound partition key

Question

With the following table definition:

CREATE TABLE device_by_create_date (
    year int,
    comm_nr text,
    created_at timestamp,
    PRIMARY KEY ((year, comm_nr), created_at)
) WITH CLUSTERING ORDER BY (created_at DESC)

Comm_nr is a unique identifier.

I would expect to see data ordered by created_at column, which is not the case when I add data.

Example entries:

Table CQL:

How can I issue select * from table; queries, which return data ordered by the created_at row?

Answer 1

TLDR: You need to create a new table.

Your partition key is (year, comm_nr). You're created_at key is ordered but it is ordered WITHIN that partition key. A query where SELECT * FROM table WHERE year=x AND comm_nr=y; will be ordered by created_at.

Additionally if instead of (year, comm_nr), created_at your key was instead year, comm_r, created_at even if your create table syntax only specifiied created_at as the having a clustering order, it would be created as WITH CLUSTERING ORDER BY (comm_nr DESC, created_at DESC). Data is sorted within SSTables by key from left to right.

The way to do this in true nosql fashion is to create a separate table where your key is instead year, created_at, comm_nr. You would write to both on user creation, but if you needed the answer for who created their account first you would instead query the new table.