Azure Streaming Analytics Query: Select latest data only

Question

Hi All,

I have very basic SQL experience and i'm a little stuck, so any help is appreciated.

I'm working on a way to stream status of some jobs that are running on an HPC resource (sort of like trying to create a dashboard to look at real time flight status). I generate and push data every 60 seconds. Unfortunately, this way i end up with a lot of repeated data as the status of each 'job' changes unpredictably. I need a way to only keep the latest data.

I've attached some images of my data. Essentially i just want to select the rows that represent the last timestamp and nothing else.

Here is my query so far:

SELECT
Job, Ref, Location, Queue, Description, Status, ElapTime, cast (Time as datetime) as Time
INTO
output_source
FROM
input_source

Thanks!

ng]1

Answer 1

Hello ,
Welcome to the Q&A .

Your goal is to get the latest data , I think you can use the max aggregate function and the belwo query should be helpful . I have not tested the query but it should give you the idea . In case if you ran into issue , please do let us know .

SELECT
Job, Ref, Location, Queue, Description, Status, ElapTime, max(cast (Time as datetime)) as Time
INTO
output_source
FROM
input_source
GROUP BY Job, Ref, Location, Queue, Description, Status, ElapTime

Thanks & stay safe

Himanshu

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members