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

Well, tell me first, what are you trying to achieve with Azure stream Analytics? Based on the question I wonder you know what it is build for. — Peter Bons

Hyder S M Hyder S M · Accepted Answer · 2018-11-12T19:07:19

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://docs.microsoft.com/en-us/stream-analytics-query/collecttop-azure-stream-analytics

Selecting partial data from stream analytics input

1 Answers