cql3 query with more than 1 EQ restriction and ORDER BY

Question

using CF:

CREATE TABLE history (
  domain text,
  iid text,
  timeid timeuuid,
  data text,
  comments text,
  PRIMARY KEY (domain, iid, timeid)
);

I'd like to query it like this:

select domain, iid, timeid, data, comments from mappings
where domain = 'a' and iid = 'b'  order by timeid desc;

But it fails with the following error (cassandra 1.1.5):

Bad Request: Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY

Am I doing it wrong? What could be the workaround? Thx

PS I got it working with the single EQ restriction and ORDER BY but I need at least 2 restrictions and order by.

Siesta

Is 'iid' an indexed column ? – Jog

I don't want to discourage you but according to: datastax.com/dev/blog/whats-new-in-cql-3-0 "ORDER BY clauses can only select a single column, and that column has to be the second column in a composite PRIMARY KEY. This holds even for tables with more than 2 column components in the primary key. " Hopefully this will change soon. Is changing the CF an option? – Heroworship

@Istern as long as iid is part of primary key i expect it is indexed – Siesta

@Heroworship it's funny as I read that but the devil in details.. thanks for pointing me there again! Yes I can change CF what would you advise? – Siesta

Answer 1

You can change 'order by' column to second column in primary key:

select * from history where domain = 'a' and iid = 'b' order by iid desc;

It is a bit confusing because you restrict iid with equality, but it works - you will get your result sorted by timeid.

I believe this is because iid and timeid form one composite column and when you order by iid in descending order, it orders all composite column components including timeid.

Foursquare

+1 to this answer for correctness, -1 to Cassandra for not supporting this simple feature in a simple way. – Henebry

I believe it should be "order by iid, desc". a comma is missing. – Minivet

see examples here - link - no comma is necessary – Foursquare