SQL to CQL: Sorting and Limiting Results

Sorting through SQL is through the ORDER BY, and the sort direction (ascending or desending) can be specified. However, the declaration relies on the SQL engine knowing the datatype for the field and then applying a suitable sorting algorithm for numerical or string values.

CQL has a sort() that can sort ascending or descending. To specify the type, use the type parameter.

SQL CQL
Sorting in reverse order
sql
SELECT timestamp, username, action 
FROM audit_logs 
WHERE status = 'failed' 
ORDER BY timestamp DESC 
LIMIT 100;

The limit for the number rows to return is set using the head() to get the first 100; use tail() to get the alst 100.

logscale
status="failed" 
| select([timestamp, username, action]) 
| sort(timestamp, order=desc) 
| head(100)
Sorting on multiple fields
sql
SELECT timestamp, username, action 
FROM audit_logs 
WHERE status = 'failed' 
ORDER BY timestamp DESC, status
LIMIT 100;
logscale
status="failed" 
| select([timestamp, username, action]) 
| sort([timestamp,status], order=[desc,asc]) 
| head(100)
Sorting by different data types

Datatype is automatically determined from the schema

sql
SELECT timestamp, username, action 
FROM audit_logs 
WHERE status = 'failed' 
ORDER BY timestamp DESC, status
LIMIT 100;

Type must be specified in the parameter

logscale
status="failed" 
| select([timestamp, username, action]) 
| sort([timestamp,status], order=[desc,asc], type=[number,string]) 
| head(100)