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.
Note
- 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.
Need top N rows per group?
top with aggregation returns top N groups overall, not N rows per group. For top N rows within each group, use window with dense_rank() and filter on rank.
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
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 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
Example output
{ "action": "logout", "user": "Harel", "time_taken_ms": 500 },
{ "action": "browse", "user": "Harel", "time_taken_ms": 250 },
{ "action": "browse", "user": "Ariel", "time_taken_ms": 200 },
{ "action": "login", "user": "Maya", "time_taken_ms": 150 },
{ "action": "login", "user": "Ariel", "time_taken_ms": 50 }