ORDER BY and CLUSTERING ORDER BY
Clustering columns sort the rows in ascending order by default: oldest date first, smallest number first. Many apps need the opposite. They want the newest messages or the latest orders first. Cassandra gives you two ways to do this.
ORDER BY: reverse the order in the query
SELECT * FROM orders
WHERE customer = 'bob'
ORDER BY order_date DESC;
Output:
customer | order_date | amount
----------+------------+--------
bob | 2024-02-25 | 45.99
bob | 2024-01-20 | 300.00
(2 rows)
DESC means descending, so the newest order of bob comes first. ASC (ascending) is the normal order.
ORDER BY in Cassandra is not the same as in SQL. It has two rules. You can only order by a clustering column:
SELECT * FROM orders
WHERE customer = 'bob'
ORDER BY amount;
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="Order by is currently only supported on the clustered columns of the PRIMARY KEY, got amount"
And the query must give one value for the partition key with =:
SELECT * FROM orders
ORDER BY order_date DESC;
Output:
InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or an IN."
The message also mentions IN, but in this editor ORDER BY with IN fails with a paging error, so use =.
The reason for both rules is the same. Cassandra does not sort rows when you run a query. It can only read the rows of a partition in the order they are stored, from the start or from the end. ORDER BY order_date DESC simply reads bob's partition from the end.
CLUSTERING ORDER BY: store the rows in reverse
If a table will almost always be read newest first, you can store it that way. Add WITH CLUSTERING ORDER BY when you create the table:
CREATE TABLE latest_orders (
customer text,
order_date date,
amount decimal,
PRIMARY KEY (customer, order_date)
) WITH CLUSTERING ORDER BY (order_date DESC);
INSERT INTO latest_orders
(customer, order_date, amount)
VALUES ('alice', '2024-01-15', 120.50);
INSERT INTO latest_orders
(customer, order_date, amount)
VALUES ('alice', '2024-02-10', 20.25);
INSERT INTO latest_orders
(customer, order_date, amount)
VALUES ('alice', '2024-03-02', 75.00);
Now a normal query, with no ORDER BY, returns the newest rows first. Add a LIMIT and you get the latest orders:
SELECT * FROM latest_orders
WHERE customer = 'alice'
LIMIT 2;
Output:
customer | order_date | amount
----------+------------+--------
alice | 2024-03-02 | 75.00
alice | 2024-02-10 | 20.25
(2 rows)
This is a very common pattern in Cassandra: choose the read order when you create the table.
In the exercise, show alice's orders with the newest order first.