Order by not work in cassandra

Question

I'm a newbie in cassandra and I have an issue with "sort".

My table is very simple, It like that:

CREATE TABLE test.user_daily_report (
    stringdate bigint,
    m_date date,
    users int,
    PRIMARY KEY (stringdate, m_date)
) WITH CLUSTERING ORDER BY (m_date DESC)
   AND read_repair_chance = 0.0
   AND dclocal_read_repair_chance = 0.1
   AND gc_grace_seconds = 864000
   AND bloom_filter_fp_chance = 0.01
   AND caching = { 'keys' : 'ALL', 'rows_per_partition' : 'NONE' }
   AND comment = ''
   AND compaction = { 'class' : 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy', 'max_threshold' : 32, 'min_threshold' : 4 }
   AND compression = { 'chunk_length_in_kb' : 64, 'class' : 'org.apache.cassandra.io.compress.LZ4Compressor' }
   AND default_time_to_live = 0
   AND speculative_retry = '99PERCENTILE'
   AND min_index_interval = 128
   AND max_index_interval = 2048
   AND crc_check_chance = 1.0;

But the result not have any effect of sort:

When I try to order by m_date i meet this issue:

Please help me, It makes me confuse a lot.

Thanks

Answer 1

In contrast to traditional relational database, in Cassandra ORDER BY works only inside partition - when you specify the WHERE condition on partition key (stringdate in your example), i.e., select * from table where stringdate = 'something' order by m_date desc - in this case data related to particular stringdate will be sorted because they are in the same partition.

In your case, you're receiving all results from Cassandra because you added 'ALLOW FILTERING' - in this case, Cassandra performs scanning of the whole cluster using your condition, fetches data, and returns it to you - but it didn't perform any sorting.

I recommend to watch "DS220: Data Modeling" course from DataStax Academy - they provide a lot of useful information...

P.S. Can you describe what task do you want to solve? I believe that you need to change data model.