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?
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.