CollectTOP (Azure Stream Analytics)
Updated: July 6, 2016
Applies To: Azure
Returns an array of ranked records, where rank defines the ranking position of the event in the window according to the specified ordering. Ordering/ranking is based on event columns and can be specified in ORDER BY clause.
Syntax
CollectTop(<number of events as integer literal>) OVER (ORDER BY (<column name> [ASC |DESC])+)
<number of events as integer literal>
The number of top events the user wants to collect from the window.
<column name>
Name of the column in the input event, by which ordering will be done. Only ordering by bigint/float/datetime types is allowed.
Array of records of type { “rank” : bigint, “value”: record }
Null is treated as the minimal value, for more information look at https://msdn.microsoft.com/library/ms188385.aspx
SELECT
value1,
CollectTop(2) OVER (ORDER BY value2 ASC, value3 DESC) as top1
FROM testinput timestamp by time
GROUP BY TumblingWindow(second, 5), value1
Example input:

Example output:

Show: