Cassandra Clustering order not sorting list

Question

I have a patient table and treatment_patient where pat_id acts as foreign key. I want to execute a query such that the treatment_patient always returns values ordered by pat_id. I tried adding WITH CLUSTERING ORDER BY (patid ASC); at the end but didn't work.

CREATE TABLE patient(
record_id uuid PRIMARY KEY,
patid int,
name text,
dob timestamp
);

Insert patients:

insert into patient (record_id, patid, name, dob) values (uuid(), 123, 'John Doe', '2015-01-01 22:00');
insert into patient (record_id, patid, name, dob) values (uuid(), 456, 'Joy Smith', '2014-11-01 21:00');

Treatment_Patients table:

CREATE TABLE treatments_patients(
treatpat_uuid int,
patid int,
diagnosis text,
PRIMARY KEY(treatpat_uuid,patid)
) WITH CLUSTERING ORDER BY (patid ASC);

Insert treatment_patients;

insert into treatments_patients (treatpat_uuid, patid, diagnosis) values (123, 011, 'Cold');
insert into treatments_patients (treatpat_uuid, patid, diagnosis) values (456, 006, 'Cough');
insert into treatments_patients (treatpat_uuid, patid, diagnosis) values (789, 002, 'flu');
insert into treatments_patients (treatpat_uuid, patid, diagnosis) values (12, 231, 'Acne');
insert into treatments_patients (treatpat_uuid, patid, diagnosis) values (789, 001, 'Allergy');

Output:

treatpat_uuid | patid | diagnosis
---------------+-------+-----------
123 |    11 |      Cold
456 |     6 |     Cough
789 |     1 |   Allergy
789 |     2 |       flu
12 |   231 |      Acne

(5 rows)

Answer 1

Cassandra only does sorting within a partition. Since your result set includes rows from multiple partitions (treapat_uuid), your data will not be sorted at that level. However, it will be sorted within rows that share the same partition, i.e. your rows within treapat_uuid were sorted in ascending order.

treatpat_uuid | patid | diagnosis
--------------+-------+-----------
...
          789 |     1 |   Allergy
          789 |     2 |       flu
...