ORDER by support over clustered column of primary key.

[Vivek Mishra <mi...@gmail.com>]

Hi,

Create a column family:
------------------------------------

CREATE TABLE altercations (
       instigator text,
       started_at timestamp,
       ships_destroyed int,
       energy_used float,
       alliance_involvement boolean,
       PRIMARY KEY (instigator,started_at,ships_destroyed);

I think "instigator" is part of primary compound key.

Insert :
---------

   INSERT INTO altercations (instigator, started_at, ships_destroyed,
                             energy_used, alliance_involvement)
                     VALUES ('Jayne Cobb', '2012-07-24', 2, 4.6, 'false');




Select:

SELECT * FROM altercations
          WHERE instigator='Jayne Cobb' ORDER BY started_at;

It works. But when i try:

SELECT * FROM altercations
          WHERE instigator='Jayne Cobb' ORDER BY instigator;

It gives me:

{Bad Request: Order by is currently only supported on the clustered columns
of the PRIMARY KEY, got instigator}


Any idea, what am i missing?

-Vivek

Re: ORDER by support over clustered column of primary key.

[Vivek Mishra <mi...@gmail.com>]

Looks like i got the answer:

Composite keys means it now makes sense for CQL to sport the ORDER BY
syntax in SELECT queries as well, but it’s still not nearly as flexible as
you might be used to, doing ad-hoc queries in SQL.  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. Ordering can be done in ascending or
descending order, default ascending, and specified with the ASC or DESC
keywords.

-Vivek

On Tue, Oct 9, 2012 at 3:24 PM, Vivek Mishra <mi...@gmail.com> wrote:

> Hi,
>
> Create a column family:
> ------------------------------------
>
> CREATE TABLE altercations (
>        instigator text,
>        started_at timestamp,
>        ships_destroyed int,
>        energy_used float,
>        alliance_involvement boolean,
>        PRIMARY KEY (instigator,started_at,ships_destroyed);
>
> I think "instigator" is part of primary compound key.
>
> Insert :
> ---------
>
>    INSERT INTO altercations (instigator, started_at, ships_destroyed,
>                              energy_used, alliance_involvement)
>                      VALUES ('Jayne Cobb', '2012-07-24', 2, 4.6, 'false');
>
>
>
>
> Select:
>
> SELECT * FROM altercations
>           WHERE instigator='Jayne Cobb' ORDER BY started_at;
>
> It works. But when i try:
>
> SELECT * FROM altercations
>           WHERE instigator='Jayne Cobb' ORDER BY instigator;
>
> It gives me:
>
> {Bad Request: Order by is currently only supported on the clustered
> columns of the PRIMARY KEY, got instigator}
>
>
> Any idea, what am i missing?
>
> -Vivek
>
>