SORT and LIMIT: Doing top-n

You use the SORT command to order your data, and the LIMIT command to take only the first n rows from your results. Together, these commands let you find top performers, recent events, highest values, or any other ranking-based analysis.

Each command is also useful on its own. SORT helps you understand data patterns by revealing ordering and outliers. You might sort transaction amounts to see the distribution of values, or sort timestamps to understand event sequences. LIMIT is valuable for exploring large datasets by giving you manageable samples. You can take the first 100 rows to understand data structure before writing more complex queries, or limit results to avoid overwhelming outputs during development.

Basic syntax

Sort your data by specifying the field you want to order by. Add DESC for descending order (highest to lowest) or leave it blank for ascending order (lowest to highest):

| SORT field_name DESC

Limit your results to a specific number of rows using the LIMIT command:

| LIMIT 10

Simple sorting

Order your data by a single field to see patterns and outliers. This is useful for finding the most recent events, highest values, or alphabetical arrangements.

Sort login events by timestamp to see them in chronological order:

FROM security_logs
| WHERE action == 'login'
| SORT timestamp DESC

This query gets login events and sorts them by timestamp in descending order (newest first), letting you see the full sequence of login activity.

Multiple sort fields

You can sort by multiple fields to create more sophisticated ordering. List the fields in order of priority, with the most important sort field first:

FROM transactions
| SORT amount DESC, timestamp DESC

This sorts transactions first by amount (highest first), then by timestamp (newest first) for transactions with the same amount. This ordering reveals value patterns across all transactions, with ties broken by recency.

Top-n analysis

The combination of SORT and LIMIT creates powerful top-n analysis patterns. This lets you answer questions like "who are my top customers" or "what are the most common errors" with simple, readable queries.

Find the top 5 users by transaction volume:

FROM transactions
| AGG total_amount = sum(amount) BY user_id
| SORT total_amount DESC
| LIMIT 5

This aggregates transaction amounts by user, sorts by the total in descending order, and takes the top 5 results. The pattern works for any ranking scenario where you need to identify leaders or outliers.

Note: This example uses the AGG command which we haven't covered yet. You can learn more about aggregation in Aggregation.

Sorting with expressions

You can sort by calculated values without adding them as permanent fields. This is useful when you want to order by a computation but don't need that computation in your final results:

FROM events
| SORT (now() - timestamp) / 1hr
| LIMIT 20

This sorts events by how many hours ago they occurred, giving you the most recent events first. The calculation happens during sorting but doesn't create a new field in your results.

Complex sorting scenarios

For more advanced sorting, you can combine multiple fields, expressions, and directions to create exactly the ordering you need.

Find the most problematic users by recent failed login attempts:

FROM security_logs
| WHERE action == 'login_failed' AND timestamp > now() - 24hr
| AGG failure_count = count(), latest_failure = max(timestamp) BY user_id
| SORT failure_count DESC, latest_failure DESC
| LIMIT 10

This query identifies users with the most failed login attempts in the last 24 hours, sorted first by failure count (most failures first), then by recency of their latest failure. This creates a prioritized list for security investigation.

Performance considerations

Sorting large datasets can be expensive, especially when sorting by multiple fields or complex expressions. When possible, apply filters with WHERE before sorting to reduce the amount of data that needs to be ordered:

FROM events
| WHERE timestamp > now() - 7d  -- Filter first
| SORT severity DESC, timestamp DESC
| LIMIT 50

This pattern filters to recent events before sorting, which is more efficient than sorting all events and then filtering.