cassandra Selection of groupwise max values

user_5734104 0
Viewed 20

I have created a table 'statistics' under covid19 keyspace:

    CREATE TABLE statistics(country_name text, dt date, confirmed_cases bigint, deaths bigint, 
    PRIMARY KEY(deaths))with clustering order by (deaths DESC);

I want to retrieve top deaths country wise. To do this, i tried:

    select * from statistics per partition limit 1;

and it is returning:

    country_name  | deaths | confirmed_cases | dt
    ---------------+--------+-----------------+------------
    BANGLADESH    |      43 |      8354      | 2022-02-08
    UNITED STATES |    3635 |    555623      | 2022-01-26
    GERMANY       |     455 |     219972     | 2022-02-16

but i need the result like following:

    country_name  | deaths | confirmed_cases | dt
    ---------------+--------+-----------------+------------
    UNITED STATES |    3635 |    555623      | 2022-01-26
    GERMANY       |     455 |     219972     | 2022-02-16
    BANGLADESH    |      43 |      8354      | 2022-02-08

Here is my sample input:

    BEGIN UNLOGGED BATCH INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('GERMANY','2022-02-17',235626,261); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('GERMANY', '2022-02-16', 219972, 455); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('GERMANY', '2022-02-15', 159217, 243); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('GERMANY', '2022-02-14', 76465, 246); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values ('GERMANY', '2022-02-13', 62841, 42); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-16', 3929, 15); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-15', 4746, 34); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-14', 4692, 19); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-13', 4838, 28); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-12', 5023, 20); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-11', 5268, 27); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-10', 7264, 41); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('BANGLADESH', '2022-02-09', 8016, 33);INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('UNITED STATES', '2022-02-05', 204770, 1761); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('UNITED STATES', '2022-02-04', 306311, 2811); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('UNITED STATES', '2022-02-03', 306317, 3279); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('UNITED STATES', '2022-02-02', 325193, 3412); INSERT INTO statistics (country_name,dt,confirmed_cases,deaths)values('UNITED STATES', '2022-02-01', 312473, 3582);
cassandra
1 Answers

Unfortunately, Cassandra will only allow for enforcement of sort order by clustering keys within a partition. Querying across partitions will always produce unordered results. This is also why Cassandra isn't a good fit for OLAP or reporting use cases.

Distributed querying tools like Spark (on top of Cassandra) does allow for ordering on arbitrary columns, as was shown in this recent question:

Saving information to Cassandra keeps no order

user_10545580
Related