You should be able to execute the following SELECT data FROM import_file WHERE roll = 1 AND type = 'foo' ORDER BY type, id DESC;
Essentially the order by clause has to specify the clustering columns in order in full. It doesn't by default know that you have already essentially filtered by type. Alec Collier | Workplace Service Design Corporate Operations Group - Technology | Macquarie Group Limited * From: Robert Wille [mailto:rwi...@fold3.com] Sent: Friday, 4 September 2015 7:17 AM To: user@cassandra.apache.org Subject: Re: Order By limitation or bug? If you only specify the partition key, and none of the clustering columns, you can order by in either direction: SELECT data FROM import_file WHERE roll = 1 order by type; SELECT data FROM import_file WHERE roll = 1 order by type DESC; These are both valid. Seems like specifying the prefix of the clustering columns is just a specialization of an already-supported pattern. Robert On Sep 3, 2015, at 2:46 PM, DuyHai Doan <doanduy...@gmail.com<mailto:doanduy...@gmail.com>> wrote: Limitation, not bug. The reason ? On disk, data are sorted by type first, and FOR EACH type value, the data are sorted by id. So to do an order by Id, C* will need to perform an in-memory re-ordering, not sure how bad it is for performance. In any case currently it's not possible, maybe you should create a JIRA to ask for lifting the limitation. On Thu, Sep 3, 2015 at 10:27 PM, Robert Wille <rwi...@fold3.com<mailto:rwi...@fold3.com>> wrote: Given this table: CREATE TABLE import_file ( roll int, type text, id timeuuid, data text, PRIMARY KEY ((roll), type, id) ) This should be possible: SELECT data FROM import_file WHERE roll = 1 AND type = 'foo' ORDER BY id DESC; but it results in the following error: Bad Request: Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY I am ordering in the declared order in the primary key. I don't see why this shouldn't be able to be supported. Is this a known limitation or a bug? In this example, I can get the results I want by omitting the ORDER BY clause and adding WITH CLUSTERING ORDER BY (id DESC) to the schema. However, now I can only get descending order. I have to choose either ascending or descending order. I cannot get both. Robert This email, including any attachments, is confidential. If you are not the intended recipient, you must not disclose, distribute or use the information in this email in any way. If you received this email in error, please notify the sender immediately by return email and delete the message. Unless expressly stated otherwise, the information in this email should not be regarded as an offer to sell or as a solicitation of an offer to buy any financial product or service, an official confirmation of any transaction, or as an official statement of the entity sending this message. Neither Macquarie Group Limited, nor any of its subsidiaries, guarantee the integrity of any emails or attached files and are not responsible for any changes made to them by any other person.
