CQL - get records by id and last timestamp

Question

I am very new to cassandra, so this may sound like a question to newbies. I am running cqlsh 5.0.1 | Kassandra 2.1.4 at the local.

I have a table as shown below:

CREATE TABLE master ( id uuid, creation timestamp, event_type text, name text, PRIMARY KEY(id,creation) ); 

... and entries:

 id | creation | event_type | name --------------------------------------+--------------------------+------------+------------------ 305abd6d-34b8-4f36-96c6-9ea0c11be952 | 2015-04-15 14:01:54-0400 | create | test2 305abd6d-34b8-4f36-96c6-9ea0c11be952 | 2015-04-15 14:03:03-0400 | update | test2 update 7440c51c-6441-44fb-833b-6140fbe822eb | 2015-04-15 14:01:54-0400 | create | test3 7440c51c-6441-44fb-833b-6140fbe822eb | 2015-04-15 14:03:44-0400 | update | test3 update 7440c51c-6441-44fb-833b-6140fbe822eb | 2015-04-15 14:04:34-0400 | update | test3 2nd update bf42a120-dec1-47d8-bde2-c0d76f1c93a5 | 2015-04-15 14:01:54-0400 | create | test1 

How can I select all records with different identifiers and last modified timestamp. The result should look like this:

 305abd6d-34b8-4f36-96c6-9ea0c11be952 | 2015-04-15 14:03:03-0400 | update | test2 update 7440c51c-6441-44fb-833b-6140fbe822eb | 2015-04-15 14:04:34-0400 | update | test3 2nd update bf42a120-dec1-47d8-bde2-c0d76f1c93a5 | 2015-04-15 14:01:54-0400 | create | test1 

Answer 1

Given the current structure, you will not be able to select any other columns except id with the DISTINCT query. You can create another query table using all id as PK and then run basic SELECT on this (it should always keep the last modified date)

 CREATE TABLE querytable ( id uuid, creation timestamp, event_type text, name text, PRIMARY KEY(id) ); SELECT * from querytable --should only contain unique ID and the last updated creation date. 

You will need to update this table when updating the wizard.