CODEWITHSUNDEEP
cassandracql
Phuong Le
Phuong Le

Reputation: 487

Cassandra select order by

I create table as this

CREATE TABLE sm.data (
    did int,
    tid int,
    ts timestamp,
    aval text,
    dval decimal,
    PRIMARY KEY (did, tid, ts)
) WITH CLUSTERING ORDER BY (tid ASC, ts DESC);

Before I did all select query with ts DESC so it was good. Now I also need select query with ts ASC in some cases. How do I accomplish that? Thank you

Upvotes: 1

Views: 4826

Answers (2)

Patrick Boulay
Patrick Boulay

Reputation: 76

if you do this select

select * from data where did=1 and tid=2 order by ts asc;

you will end up with some errors

InvalidRequest: Error from server: code=2200 [Invalid query] message="Order by currently only support the ordering of columns following their declared order in the PRIMARY KEY"

I have tested it against my local cassandra db I would suggets altering the order of the primary key columns

the reason is that : "Querying compound primary keys and sorting results ORDER BY clauses can select a single column only. That column has to be the second column in a compound PRIMARY KEY."

CREATE TABLE data2 ( did int, tid int, ts timestamp, aval text, dval decimal, PRIMARY KEY (did, ts, tid) ) WITH CLUSTERING ORDER BY (ts DESC, tid ASC)

Now we are free to choose the type of ordering for TS

cassandra@cqlsh:airline> SELECT * FROM data2 WHERE did = 1 and ts=2 order by ts DESC;

did | ts | tid | aval | dval -----+----+-----+------+------

(0 rows) cassandra@cqlsh:airline> SELECT * FROM data2 WHERE did = 1 and ts=2 order by ts ASC;

did | ts | tid | aval | dval -----+----+-----+------+------

(0 rows)

Another way would be either to create a new table or a materialized view , the later would lead behind the scene to data duplication anyway hope that clear enough

Upvotes: 0

Ashraful Islam
Ashraful Islam

Reputation: 12840

You can simply use ORDER BY ts ASC

Example : 

SELECT * FROM data WHERE did = ? and tid = ? ORDER BY ts ASC

Upvotes: 1

Related Questions