Search code examples
azure-stream-analytics

Last DateTime from JSON in Azure Stream Analytics

I am having an issue in ASA when trying to get the "Max" DateTime.

My code is:

LAST(Timestamp) OVER (PARTITION BY DeviceId LIMIT DURATION(minute, 5)) AS DateTime,

It is coming up with an error saying it is not in an aggregate function or in the group by. Im not sure what I am doing wrong here. Any help would be appreciated!

Solution

  • LAST is not an aggregate function and cannot be used in GROUP BY statements. It is most typically used in SELECT statements together with WHEN clause to "look back" and find event matching specific condition. Please check examples here

    If what you want is to find biggest timestamp in the time window, you can do something like this:

    SELECT MAX(CAST(Timestamp AS DateTime)) AS DateTime FROM input GROUP BY DeviceId, TumblingWindow(minute, 1)