SQL
Top-N by group
Top-N queries return only the N top-most or the N bottom-most records from a table or view based on a condition.
In RisingWave, a Top-N query includes a ranking function clause and a rank filtering condition. In the ranking function clause, you can include a PARTITION BY
clause to fetch top N rows per group.
Syntax
The syntax of the ranking_function_clause
is:
rank
cannot be included in column_list
.
You must follow the pattern exactly to construct a valid Top-N query.
Parameter | Description |
---|---|
function_name | RisingWave supports two window functions in top-N queries: row_number(): Returns the sequential row ordinal (1-based) of each row for each ordered partition.rank(): Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value. The next row or set of peer rows receives a rank value which increments by the number of peers with the previous rank value. |
PARTITION BY clause | Specifies the partition columns. Each partition will have a Top-N result. |
ORDER BY clause | Specifies how the rows are ordered. |
rank_range | Specifies the range of the rank number. The rank range is required for the query to be recognized as a top-N query. The range can be specified in these forms. Examples: WHERE M < rank AND rank < N or WHERE rank between M and N. Optionally, you can specify any additional conditions to further filter the results. |
Example
Create a table
Insert data
Run a top-N query
Was this page helpful?