title: Get latest unique result with Cassandra
body: <p>I have a service that handles users state on different services.
  The traffic can be very high on multiple DCs so I thought Cassandra will fit to store this data.<br>
  I only need to keep the latest update per service and user.<br>
  I thought about creating this table:</p>
  
  <pre><code>CREATE TABLE db.state (
     service uuid,
     user uuid,
     updated_at timestamp,
     data varchar,
  
     PRIMARY KEY (service, user, updated_at)
  ) WITH CLUSTERING ORDER BY (updated_at DESC);
  </code></pre>
  
  <p>The question is how can I query for the latest 100 unique users state.<br>
  With this query:  </p>
  
  <p><code>SELECT service, user, data, updated_at FROM db.state WHERE service = :service LIMIT 100</code> . </p>
  
  <p>If a certain user had many updates, I'm not going to get the latest 100 users but less.
  I don't want to merge the unique users in the client because in order to get 100 users I sometimes need to get 10000 rows.</p>
  
  <p>I thought about 2 solutions which both have problems:  </p>
  
  <ol>
  <li>create the main table with <code>PRIMARY KEY (service, user)</code> and
  create materialized view with <code>PRIMARY KEY (service, user,
  updated_at)</code>. But this will hurt performance.</li>
  <li>create the table with <code>PRIMARY KEY (service, user)</code> and read
  with full consistency before the write to check that older update is not written. But this gives up availability and an anti-pattern for
  Cassandra.</li>
  </ol>
  
  <p><strong>Is there a way to do it without a read-before-write / materialized view?</strong></p>
  
  <hr>
  
  <h2>edit</h2>
  
  <p>The writes doesn't necessarily comes in order, so the timestamp is provided externally.<br>
  I don't need to keep history, just the last update (by the external timestamp).</p>