title: Cassandra equivalent of group by
body: <p>I'm in initial stages of data modeling in Cassandra for an application. This application has existing relational persistence layer, which has to be replaced with Cassandra.</p>
  
  <p>Application uses a table named <code>login_log</code> for users, which provides last login time for any users in all applications.</p>
  
  <p>Here's the <code>CQL</code> that I'm using to create this table</p>
  
  <pre class="lang-sql prettyprint-override"><code>create table login_log (
      user_id int,
      application_name text,
      login_date timestamp,
      primary key (user_id, application_name, login_date)
  ) with clustering order by (application_name asc, login_date desc)
  </code></pre>
  
  <p><code>user_id</code> is partition key. <code>application_name</code> and <code>login_date</code> are clustering keys. This table maintains a history of logins, data is never deleted in it.</p>
  
  <p>I'm trying to retrieve last login date of given <code>user_id</code> for all applications in one query.</p>
  
  <p>If I were writing a <code>SQL</code> query to do the same it would look something like below</p>
  
  <pre class="lang-sql prettyprint-override"><code>select user_id, application_name, max(login_date) from login_log group by user_id, application_name
  </code></pre>
  
  <p>However it cannot be done in Cassandra, since there's no group by clause or aggregation functions. Arguably clustering columns are already grouped but I'm unable to create a query which retrieves latest <code>login_date</code> for all applications in one query.</p>
  
  <p>If were doing it for one application <code>CQL</code> would look like below</p>
  
  <pre class="lang-sql prettyprint-override"><code>select * from login_log where user_id = ? and application_name = ? limit 1
  </code></pre>
  
  <p>Since cluster are already ordered by <code>login_date</code>, <code>order by</code> is not needed. I need to extend the same query to retrieve data for all applications in one go.</p>
  
  <p>Is it possible to do this in Cassandra? If not is there a data modeling technique that will allow me to do this?</p>
  
  <p>Any hint is appreciated.</p>