CODEWITHSUNDEEP
cassandradatastaxcassandra-3.0
Shasu
Shasu

Reputation: 501

How to use ORDER BY with IN statement in cassandra query in DevCenter

Trying to use "order by" clause with IN statement of Cassandra query in DevCenter. Unable to PAGING OFF in DevCenter

I have table model_vals :


-----------------------------------------------
type_code         date                 item_code
-------------------------------------------------
TYPE_0           2018-03-31           CREDITS
TYPE_10          2018-03-31           CREDITS  
TYPE_25          2018-03-31           CREDITS
TYPE_50          2018-03-31           CREDITS 
TYPE_75          2018-03-31           CREDITS
TYPE_90          2018-03-31           CREDITS
TYPE_100         2018-03-31           CREDITS
MEAN             2018-03-31           CREDITS
TYPE_0           2017-03-31           CREDITS
TYPE_10          2017-03-31           CREDITS  
TYPE_25          2017-03-31           CREDITS
TYPE_50          2017-03-31           CREDITS 
TYPE_75          2017-03-31           CREDITS
TYPE_90          2017-03-31           CREDITS
TYPE_100         2017-03-31           CREDITS
MEAN             2017-03-31           CREDITS
TYPE_0           2016-03-31           CREDITS
TYPE_10          2016-03-31           CREDITS  
TYPE_25          2016-03-31           CREDITS
TYPE_50          2016-03-31           CREDITS 
TYPE_75          2016-03-31           CREDITS
TYPE_90          2016-03-31           CREDITS
TYPE_100         2016-03-31           CREDITS
MEAN             2016-03-31           CREDITS

Trying to execute below query in DevCenter:


select * from model_vals
where  type='COUNTRY'
and type_code  in (
'TYPE_0',
'TYPE_10',
'TYPE_25',
'TYPE_50',
'TYPE_75',
'TYPE_90',
'TYPE_100',
'MEAN')
and item_code='CREDITS'
and date <='2018-03-31'
order by date desc;

1) Getting error as "order by" and "in" clause cant be used together. Unable to PAGING OFF in DevCenter , how to do it ?

2) If have to fetch the records of 2018 records with all type_code using date <='2018-03-31'. i.e. total 8 records.

If dont have '2018-03-31' records then it should get next data 8 records i.e. for all types in 2017-03-31.

If dont have data of 2017-03-31, then it should get next data 8 records i.e. for all types in 2016-03-31.

How can achieve this kind of results query ?

Upvotes: 0

Views: 73

Answers (1)

Alex Ott
Alex Ott

Reputation: 87174

Sorting of data happens only inside the single partition, while you're providing multiple partitions in the IN statement. For your use case it's better not to use multiple partition key values with IN, but instead execute multiple parallel request for every single value of partition key, get data, and then sort data in your application.

P.S. I recommend to get some of the courses on DataStax Academy, like, DS220 (Data modelling).

Upvotes: 1

Related Questions