top
Description
The top command returns the first N results after sorting by one or more expressions in the by clause. It can be used with plain expressions or with aggregation functions.
- Without aggregation,
toplimits the full result set to N rows, ordered by a given expression. - With aggregation,
topgroups results by the result expressions and returns the top N groups (not N rows per group), ranked by the ordering expression. - Sorting direction (ascending/descending) is determined by the expression or implicit aggregate ordering.
top with aggregation returns top N groups overall, not N rows per group.
top behaves like a group, sort, and limit in one command. This:
top 10 user by count()
usually returns the same rows as:
groupby user agg count() as _count
| orderby _count desc
| limit 10
When every by expression is an aggregation, top/bottom use an approximate top-k algorithm for better performance. Results are exact almost all the time; only rare cases with highly skewed data can produce an inexact result. If you need a guaranteed exact result, write the groupby / orderby / limit sequence yourself instead of top/bottom.
Syntax
top <limit> <result_expression1> [as <alias>] [, <result_expression2> [as
<alias2>], ...] by <orderby_expression> [as <alias>]
Example 1
Use case: Identify the most active users by event count
The top command can be used to return only the N most active usernames by counting how frequently each appears in log data.
Example data
{ "user": "Ariel", "action": "login", "time_taken_ms": 50 },
{ "user": "Harel", "action": "logout", "time_taken_ms": 500 },
{ "user": "Maya", "action": "login", "time_taken_ms": 180 },
{ "user": "Ariel", "action": "browse", "time_taken_ms": 200 },
{ "user": "Harel", "action": "login", "time_taken_ms": 90 }
Example query
top 10 user by count()
Example output
| user | _count |
|---|---|
| Ariel | 2 |
| Harel | 2 |
| Maya | 1 |
Example 2
Use case: Rank unique action-user combinations by slowest response time
Group rows by action and user, then return the top 5 (action, user) groups ranked by their slowest time_taken_ms. The result is one row per unique combination, top returns the top N groups overall, not N rows per group.
Example data
{ "user": "Ariel", "action": "login", "time_taken_ms": 50 },
{ "user": "Harel", "action": "logout", "time_taken_ms": 500 },
{ "user": "Ariel", "action": "browse", "time_taken_ms": 200 },
{ "user": "Maya", "action": "login", "time_taken_ms": 150 },
{ "user": "Harel", "action": "browse", "time_taken_ms": 250 }
Example query
top 5 action, user by max(time_taken_ms) as slowest_ms
Example output
{ "action": "logout", "user": "Harel", "slowest_ms": 500 },
{ "action": "browse", "user": "Harel", "slowest_ms": 250 },
{ "action": "browse", "user": "Ariel", "slowest_ms": 200 },
{ "action": "login", "user": "Maya", "slowest_ms": 150 },
{ "action": "login", "user": "Ariel", "slowest_ms": 50 }