title: Order latest records by timestamp in Cassandra
body: <p>I'm trying to display the latest values from a list of sensors. The list should also be sortable by the time-stamp.</p>
  
  <p>I tried two different approaches. I included the update time of the sensor in the primary key:</p>
  
  <pre><code>CREATE TABLE sensors (
      customerid int,
      sensorid int,
      changedate timestamp,
      value text,
      PRIMARY KEY (customerid, changedate)
  ) WITH CLUSTERING ORDER BY (changedate DESC);
  </code></pre>
  
  <p>Then I can select the list like this:</p>
  
  <pre><code>select * from sensors where customerid=0 order by changedate desc;
  </code></pre>
  
  <p>which results in this:</p>
  
  <pre><code> customerid | changedate               | sensorid | value
  ------------+--------------------------+----------+-------
            0 | 2015-07-10 12:46:53+0000 |        1 |     2
            0 | 2015-07-10 12:46:52+0000 |        1 |     1
            0 | 2015-07-10 12:46:52+0000 |        0 |     2
            0 | 2015-07-10 12:46:26+0000 |        0 |     1
  </code></pre>
  
  <p>The problem is, I don't get only the latest results, but all the old values too.</p>
  
  <p>If I remove the changedate from the primary key, the select fails all together.</p>
  
  <pre><code>InvalidRequest: code=2200 [Invalid query] message="Order by is currently only supported on the clustered columns of the PRIMARY KEY, got changedate"
  </code></pre>
  
  <p>Updating the sensor values is also no option:</p>
  
  <pre><code>update overview set changedate=unixTimestampOf(now()), value = '5' where customerid=0 and sensorid=0;
  InvalidRequest: code=2200 [Invalid query] message="PRIMARY KEY part changedate found in SET part"
  </code></pre>
  
  <p>This fails because changedate is part of the primary key.</p>
  
  <p>Is there any possible way to store only the latest values from each sensor and also keep the table ordered by the time-stamp?</p>
  
  <p><strong>Edit:</strong>
  In the meantime I tried another approach, to only storing the latest value.</p>
  
  <p>I used this schema:</p>
  
  <pre><code>CREATE TABLE sensors (
      customerid int,
      sensorid int,
      changedate timestamp,
      value text,
      PRIMARY KEY (customerid, sensorid, changedate)
  ) WITH CLUSTERING ORDER BY (changedate DESC);
  </code></pre>
  
  <p>Before inserting the latest value, I would delete all old values</p>
  
  <pre><code>DELETE FROM sensors WHERE customerid=? and sensorid=?;
  </code></pre>
  
  <p>But this fails because <code>changedate</code> is NOT part of the WHERE clause.</p>