Skip to content

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

  • The expressions before by define the fields returned by top.
  • The expressions after by define how those results are sorted.
  • When aggregation functions are used, top first groups by the non-aggregation expressions and calculates the aggregation expressions for each group.
  • After the result set is formed, top sorts it by the results of by expressions and returns the first N results.
  • For top, sorting is descending. Use bottom for the corresponding ascending order.

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
Ariel2
Harel2
Maya1

Example 2

Use case: Get the slowest user interactions per action

By grouping and ordering data by action and user, the top command can return only the longest-duration interactions in each 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 time_taken_ms by action, user

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 }
Need help? Contact Support.
What's new? Find out here.
LLM? Read llms.txt.
Previous stitch
Next union