8

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.

Ivan Velykorodnyy
  • 401
  • 3
  • 11
  • Is 'iid' an indexed column ? – lstern Nov 08 '12 at 12:28
  • 4
    I don't want to discourage you but according to: http://www.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? – Oren Nov 08 '12 at 19:24
  • @Istern as long as iid is part of primary key i expect it is indexed – Ivan Velykorodnyy Nov 08 '12 at 22:20
  • @Oren 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? – Ivan Velykorodnyy Nov 08 '12 at 22:52

1 Answers1

9

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.

Lucas Zamboulis
  • 2,494
  • 5
  • 24
  • 27
Dmitry Shohov
  • 322
  • 2
  • 9