Xtykutl

I have a problem modeling my data using Cassandra. I would like to use it as an event store. My events have creation timestamp. Those event belong to a partition which is identified by an id.

Now I'd like to see most recent event for each id and then filter this ids according to the timestamp.

So I have something like this:

ID | CREATION_TIMESTAMP              | CONTENT

---+---------------------------------+----------------

1  | 2018-11-09 12:15:45.841000+0000 | {SOME_CONTENT} 

1  | 2018-11-09 12:15:55.654656+0000 | {SOME_CONTENT}

2  | 2018-11-09 12:15:35.982354+0000 | {SOME_CONTENT}

2  | 2018-11-09 12:35:25.321655+0000 | {SOME_CONTENT}

2  | 2018-11-09 13:15:15.068498+0000 | {SOME_CONTENT}

I tried grouping by partition id and querying for max of creation_timestamp but that is not allowed and I should specify partition id using EQ or IN. Additional reading led me to believe that this is entirely wrong way of approaching this problem but I don't know whether NoSQL is not suitable tool for the job or I am simply approaching this problem from wrong angle?

You can easily achieve this by having your CREATION_TIMESTAMP as clustering column and ordered DESC. Then you would query by your id and using limit 1 (which will return the most recent event since the data is order DESC in that partition key).

can you please share your table definition .

by looking at your data you can use ID as partition key and CREATION_TIMESTAMP as clustering column.

you can use select MAX(CREATION_TIMESTAMP) from keyspace.table where ID='value';