10

I am using Cassandra 1.2.3 and can execute select query with Limit 10.

If I want records from 10 to 20, I cannot do "Limit 10,20".

Below query gives me an error.

select * from page_view_counts limit 10,20

How can this be achieved?

Thanks Nikhil

user1058797
  • 867
  • 3
  • 10
  • 19

3 Answers3

12

You can't do skips like this in CQL. You have have to do paging by specifying a start place e.g.

select * from page_view_counts where field >= 'x' limit 10;

to get the next 10 elements starting from x.

I wrote a full example in this answer: Cassandra pagination: How to use get_slice to query a Cassandra 1.2 database from Python using the cql library.

Community
  • 1
  • 1
Richard
  • 11,050
  • 2
  • 46
  • 33
  • Thanks.. It looks nice.. Will try it out. – user1058797 Jun 11 '13 at 06:24
  • 1
    Is there any way to go to a specific page of the result? I want to use the limit clause for pagination. Using your approach, I cannot go directly to page 5, of the results (limit 40,10). I have to go in order page 1, page 2, page 3, page 4 and then page 5. – user1058797 Jun 11 '13 at 09:06
  • 1
    You can jump, but you have to store the first column of the page, rather than the offset. – Richard Jun 11 '13 at 09:20
  • 1
    i did not get how exactly can this be done. Because the data is stored sequentially in columns. Hence even if I save, a column as say 10th record, then the value will change later and it may become the 11th record. You have any example on how this can be done? Thanks. – user1058797 Jun 11 '13 at 12:20
  • 1
    Yes, if the value changes then it could be wrong. Just like if you skipped to the 10th it may duplicate or miss a result. An example is in the link in the answer. – Richard Jun 11 '13 at 12:31
0

for that you have to first plan your data model so that it can get records according to your requirement... Can you tell which sort of example your are doing? and Are you using hector client or any other ?

Hardik Bhalani
  • 863
  • 2
  • 8
  • 24
0

sorry mate I did it using hector client & java,but seeing your requirement I can suggest to plan your data model like this : Use time span as a row key in yyyyMMddHH format,in that store column name as composite key made up of UTF8Type and TimeUUID (e.g C1+timeUUID ). note: here first composite key would be counter column family column name (e.g. C1) Now you will only store limited records say 20 in your CF and make this c1 counter 20,now if any new record came for the same timespan you have to insert that with key C2+timeUUID now u will increment counter column family c2 upto 20 records

Now to fetch record you just have to pass value C1 , C2 ...etc with rowkey like 2013061116 it will give you 20 records than another 20 and so on...you have to implement this programmatically..hope you got this and helps you

Hardik Bhalani
  • 863
  • 2
  • 8
  • 24