0

This question is essentially the same as in this post, SQL Select only rows with Max Value on a Column, except in CQL. I'm working with Cassandra 3.10 so GROUP BY is supported, but HAVING and JOIN are not.

As in the question in above link, we need to find the rows (including "content" column) in each id, with max(rev). In fact, the actual problem I'm trying to solve is to max(rev) grouping by two identifiers, id1 and id2, so ordering by id also doesn't work here.

 +------+-------+-------+--------------------------------------+
 | id1  | rev   |  id2  |  content                             |
 +------+-------+-------+------------------------------ -------+
 | 1    |  1    | 1     | ...                                  |
 | 1    |  2    | 1     | ...                                  |
 | 2    |  1    | 2     | ...                                  |
 | 1    |  3    | 3     | ...
 +------+-------+-------+--------------------------------------+

The SQL solutions I had for this were:

SELECT id1, id2, rev, content FROM table 
GROUP BY id1, id2 HAVING rev = MAX(rev);

And

SELECT id1, id2, rev, content FROM table 
WHERE rev IN 
(SELECT MAX(rev) FROM table GROUP BY id1, id2)

(The second works assuming rev is unique.)

Without HAVING or JOIN, what would be a viable approach in CQL or Cassandra 3.10?

Community
  • 1
  • 1
tpoker
  • 492
  • 1
  • 5
  • 15
  • Assuming id1 and id2 are Primary key `select id1,id2,MAX(rev) FROM table GROUP BY id1, id2` should work – undefined_variable Jun 27 '17 at 17:19
  • But I want 'content' column relevant to that max(rev) as well, and the way GROUP BY aggregates 'content' doesn't guarantee the resulted 'content' value is in the same row as max(rev) @undefined_variable – tpoker Jun 27 '17 at 18:09

0 Answers0