Cassandra ORDER BY

The ORDER BY clause in CQL controls the sort order of rows returned within a partition. Unlike SQL, Cassandra's ORDER BY is strictly limited to clustering columns within a single partition. You cannot order by a column that is not a clustering column, and you cannot sort rows across different partitions.

How Cassandra Stores Rows

Cassandra always writes rows within a partition in the sort order defined by the clustering columns. This physical sort order is set at table creation time with the CLUSTERING ORDER BY clause. Changing the ORDER BY direction in a SELECT simply reads those pre-sorted rows forward or backward.

Table: messages
PRIMARY KEY (room_id, sent_at)
CLUSTERING ORDER BY (sent_at ASC)   -- default ascending storage order

Physical disk layout:

room_id='chat-1':
  [sent_at: 2024-06-01 08:00] → "Good morning"
  [sent_at: 2024-06-01 08:05] → "Ready for the call?"
  [sent_at: 2024-06-01 08:10] → "Starting now"
  [sent_at: 2024-06-01 08:30] → "Wrapping up"

ORDER BY ASC (Default)

SELECT * FROM messages
WHERE room_id = 'chat-1'
ORDER BY sent_at ASC;

Result:
sent_at                  | sender | body
─────────────────────────────────────────────────────
2024-06-01 08:00:00      | alice  | Good morning
2024-06-01 08:05:00      | bob    | Ready for the call?
2024-06-01 08:10:00      | alice  | Starting now
2024-06-01 08:30:00      | carol  | Wrapping up

ORDER BY DESC

Reversing the sort order tells Cassandra to read the SSTable from the end of the partition backward. This is equally fast — Cassandra simply reads in reverse.

SELECT * FROM messages
WHERE room_id = 'chat-1'
ORDER BY sent_at DESC;

Result (newest first):
sent_at                  | sender | body
─────────────────────────────────────────────────────
2024-06-01 08:30:00      | carol  | Wrapping up
2024-06-01 08:10:00      | alice  | Starting now
2024-06-01 08:05:00      | bob    | Ready for the call?
2024-06-01 08:00:00      | alice  | Good morning

Define Default Sort at Table Creation

Set the default sort direction in the table definition so SELECT queries without ORDER BY return rows in the order your application uses most.

CREATE TABLE messages (
  room_id  TEXT,
  sent_at  TIMESTAMP,
  sender   TEXT,
  body     TEXT,
  PRIMARY KEY (room_id, sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);

Now SELECT * FROM messages WHERE room_id = 'chat-1' returns the newest message first without any ORDER BY in the query.

Multiple Clustering Columns and ORDER BY

When a table has multiple clustering columns, ORDER BY must list them in the order they appear in the PRIMARY KEY definition. You can change only the last column's direction — all preceding columns must match their table-level CLUSTERING ORDER setting.

CREATE TABLE game_scores (
  game_id   TEXT,
  level     INT,
  player_id UUID,
  score     INT,
  PRIMARY KEY (game_id, level, player_id)
) WITH CLUSTERING ORDER BY (level ASC, player_id ASC);

-- Valid: matches clustering column order
SELECT * FROM game_scores
WHERE game_id = 'chess-pro'
ORDER BY level ASC, player_id ASC;

-- Valid: reverse both
SELECT * FROM game_scores
WHERE game_id = 'chess-pro'
ORDER BY level DESC, player_id DESC;

-- Invalid: mix of ASC and DESC (not allowed in CQL)
SELECT * FROM game_scores
WHERE game_id = 'chess-pro'
ORDER BY level ASC, player_id DESC;

ORDER BY Does Not Work Across Partitions

ORDER BY only sorts rows within a single partition. Cassandra cannot sort rows that live on different nodes in a cluster-wide sort. If you need global ordering, bring results to the application layer and sort them there.

Single partition — ORDER BY works:
  WHERE room_id = 'chat-1'
  ORDER BY sent_at DESC  ✓

Cross-partition — ORDER BY not allowed:
  WHERE room_id IN ('chat-1', 'chat-2')
  ORDER BY sent_at DESC  ✗  (CQL error)

ORDER BY with LIMIT

Combining ORDER BY with LIMIT is the standard pattern for "get the most recent N items" queries.

-- Latest 10 messages in a chat room:
SELECT * FROM messages
WHERE room_id = 'chat-1'
ORDER BY sent_at DESC
LIMIT 10;

Performance of ORDER BY

Because rows are already physically sorted on disk, ORDER BY in Cassandra adds almost zero overhead. Reading in reverse (DESC when the table is stored ASC, or vice versa) requires a backward SSTable scan, which is slightly less cache-friendly than forward reads but still very fast in practice.

Summary

ORDER BY in CQL sorts rows within a partition by one or more clustering columns. You can sort ascending or descending, but all clustering columns in ORDER BY must appear in their defined order and with consistent direction. Set the default sort order at table creation time using CLUSTERING ORDER BY. For "latest N items" patterns, combine ORDER BY DESC with LIMIT for fast, efficient reads.