Cassandra cql: select N “most recent” rows in ascending order

Question

I understand that the best way to fetch the most recent rows in Cassandra is to create my table as following:

CREATE TABLE IF NOT EXISTS data1(
               asset_id int
               date timestamp,
               value decimal,
               PRIMARY KEY ((asset_id), date)
            ) WITH CLUSTERING ORDER BY (date desc);

Then select 1000 recent data items via:

select * from data1 where asset_id = 8 limit 1000;

The client requires the data in ascending order. Server side is python. Is there a way to reverse the results in CQL and not in code (i.e. python)?

If you can provide the date of nth row, then you can order asc in cql — Ashraful Islam
Just as a side note, interesting discussion on this topic here: issues.apache.org/jira/browse/CASSANDRA-4004 — Marko Švaljek
I'm assuming you're going to iterate over the result set. Can't you just iterate in reverse order? — xmas79
@xmas79 You are correct in case of iteration (e.g. when creating a JSON response) I can loop from end to start. It is simple and gives the required result. I missed that. Thanks. — Shay

bechbd bechbd · Accepted Answer · 2017-04-18T17:40:28

Have you tried using the ORDER BY clause

select * from data1 where asset_id = 8 ORDER BY date asc limit 1000;

More information available here: https://docs.datastax.com/en/cql/3.1/cql/cql_using/useColumnsSort.html

Cassandra cql: select N “most recent” rows in ascending order

1 Answers