ORDER BY reloaded, cassandra

A given column family I would like to sort and to this I am trying to create a table with the option CLUSTERING ORDER BY. I always encounter the following errors:

1.) Variant A resulting in Bad Request: Missing CLUSTERING ORDER for column userid Statement:

CREATE TABLE test.user (
  userID timeuuid,
  firstname varchar,
  lastname varchar,
  PRIMARY KEY (lastname, userID)
)WITH CLUSTERING ORDER BY (lastname desc);

2.) Variant B resulting in Bad Request: Only clustering key columns can be defined in CLUSTERING ORDER directive Statement:

CREATE TABLE test.user (
  userID timeuuid,
  firstname varchar,
  lastname varchar,
  PRIMARY KEY (lastname, userID)
)WITH CLUSTERING ORDER BY (lastname desc, userID asc);

As far as I can see in the manual this is the correct syntax for creating a table for which I would like to run queries as "SELECT .... FROM user WHERE ... ORDER BY lastname". How could I achieve this? (The column 'lastname' I would like to keep as the first part of the primary key, so that I could use it in delete statements with the WHERE-clause.)

Thanks a lot, Tamas

Solution

Clustering would be limited to whats defined in partitioning key, in your case (lastName + userId). So cassandra would store result in sorted order whose (lastName+userId) combination. Thats why u nned to give both for retrieval purpose. Its still not useful schema if you want to sort all data in table as last name as userId is unique(timeuuid) so clustering key would be of no use.

CREATE TABLE test.user (
  userID timeuuid,
  firstname varchar,
  lastname varchar,
  bucket int,
  PRIMARY KEY (bucket)
)WITH CLUSTERING ORDER BY (lastname desc);

Here if u provide buket value say 1 for all user records then , all user would go in same bucket and hense it would retrieve all rows in sorted order of last name. (By no mean this is a good design, just to give you an idea).

Revised :

CREATE TABLE user1 (
  userID uuid,
  firstname varchar,
  lastname varchar,
  bucket int,
  PRIMARY KEY ((bucket), lastname,userID)
)WITH CLUSTERING ORDER BY (lastname desc);