This query would be reasonable to support, so I've opened https://issues.apache.org/jira/browse/CASSANDRA-10271 to fix that.
On Thu, Sep 3, 2015 at 7:48 PM, Alec Collier <alec.coll...@macquarie.com> wrote: > 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> 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> 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. > -- Tyler Hobbs DataStax <http://datastax.com/>
