I am running the below Cassandra query, for which I am getting the correct maximum value for max_sale_value, but wrong value for the corresponding country column.
I am using Cassandra 5.0.2 with the below configurations,
[cqlsh 6.2.0 | Cassandra 5.0.2 | CQL spec 3.4.7 | Native protocol v5]
Please correct me if there is any issue with the queries, I am adding DDL query as well.
CREATE TABLE sales_data (
country TEXT,
record_no UUID,
invoice TEXT,
stock_code TEXT,
description TEXT,
quantity INT,
invoice_date TEXT,
unit_price DECIMAL,
customer_id TEXT,
PRIMARY KEY (country, record_no)
);
SELECT MAX(quantity * unit_price) AS max_sale_value, country
FROM sales_data group by country; --gives max_sale_value of all the countries, but not 1 row which is the maximum among all the countries i.e. United Kingdom | 25111.090
SELECT Country, MAX(quantity * unit_price) AS max_sale_value
FROM sales_data ALLOW FILTERING; --gives max_sale_value correctly as 25111.090, but giving the wrong country name