Selecting partial data from stream analytics input

Question

I have a stream analytics job that recieves input from an IOT hub and writes data to an azure SQL database. My current query is as follows

Select [Column1,Column2...] INTO [Output] from [Input]

Is it possible to select only specific number of rows? I am looking for something like the following

Select TOP 10 [Column1,Column2...] INTO [Output] from [Input]

Any suggestions would be appreciated. Thanks

Answer 1

If you have a window, you can use the LAG function as shown here:

SELECT
CollectTop(10) OVER (ORDER BY column1 DESC) as topColumn1, CollectTop(10) OVER (ORDER BY column2 DESC) as topColumn2 INTO output FROM input
GROUP BY TumblingWindow(second, 5)

Documentation can be found here: https://learn.microsoft.com/en-us/stream-analytics-query/collecttop-azure-stream-analytics