Cassandra how to query to get the most recent message

Question

Please i need a help am new to Cassandra. I have a table called conversation, user1 is my partition key while time is my clustering key

How can i query this table to get all users, user1 have established conversation with, including the most recent message.

CREATE TABLE conversation (
  user1 text,
  conversationId text,
  message text,
  user2,
  time,
  PRIMARY KEY(user1, time) 
) WITH CLUSTERING ORDER BY (time DESC)

.............................................................
| User1 | ConversationId |    Message    | User 2  |  Time  |
|.......|................|...............|.........|........|
| Bobby | 100 - 101      | Hello         | Chris   |12:10pm |
| Bobby | 100 - 101      | U there?      | Chris   |12:11pm |
| Bobby | 100 - 102      | Am here       | Dan     |12:12pm |
| Bobby | 100 - 102      | Hello Dan     | Dan     |12:13pm |
| Bobby | 100 - 103      | Am coming     | Sam     |12:14pm |
| Bobby | 100 - 103      | Hello sam     | Sam     |12:15pm |

This should be my output after query.

.............................................................
| User1 | ConversationId |    Message    | User 2  |  Time  |
|.......|................|...............|.........|........|
| Bobby | 100 - 103      | Hello sam     | Sam     |12:15pm |
| Bobby | 100 - 102      | Hello Dan     | Dan     |12:13pm |
| Bobby | 100 - 101      | U there?      | Chris   |12:11pm |

Answer 1

You cannot do that in Cassandra with your existing table. But you could create a new table for this purpose, and insert all records in that table too.

CREATE TABLE conversation_lastmessage (
  user1 text,
  conversationId text,
  message text,
  user2 text,
  time timestamp,
  PRIMARY KEY ((user1), user2));

Assuming that you insert your data in order of time, you will get the latest messages of user1 with every correspondent.

SELECT * FROM conversation_lastmessage WHERE user1 = 'user1';