Search code examples
cassandranosqldatastaxcql

Get the last 100 rows from cassandra table

I have a table in cassandra now i cannot select the last 200 rows in the table. The clustering order by clause was supposed to enforce sorting on disk.

CREATE TABLE t1(id int , 
event text, 
receivetime timestamp ,
PRIMARY KEY (event, id)
) WITH CLUSTERING ORDER BY (id DESC)
;

The output is unsorted by id:

event   | id | receivetime
---------+----+---------------------------------
  event1 |  1 | 2021-07-12 08:11:57.702000+0000
  event7 |  7 | 2021-05-22 05:30:00.000000+0000
  event5 |  5 | 2021-05-25 05:30:00.000000+0000
  event9 |  9 | 2021-05-22 05:30:00.000000+0000
  event2 |  2 | 2021-05-21 05:30:00.000000+0000
 event10 | 10 | 2021-05-23 05:30:00.000000+0000
  event4 |  4 | 2021-05-24 05:30:00.000000+0000
  event6 |  6 | 2021-05-27 05:30:00.000000+0000
  event3 |  3 | 2021-05-22 05:30:00.000000+0000
  event8 |  8 | 2021-05-21 05:30:00.000000+0000

How do I overcome this problem? Thanks

Solution

  • The ordering for a clustering order, is the order within a single partition key value, e.g. all of the rows for event1 would be in order for event1. It is not a global ordering.

    From your results we can see you are selecting multiple partitions - which is why you are not seeing an order you expect.