Search code examples
azure-stream-analytics

In Azure Stream Analytics Query, How to get the first value of a group?

My input is as follows fqn, v, t. I need to get the first v value in the group. The following is my query. I need the first value of v in the group

SELECT
v
fqn, min(t) as timeslot
INTO
[finalalloutput]
FROM
[finalallinput] timestamp by t
where fqn not like '%Production%' 
group by TumblingWindow(minute, 1),fqn
Solution

  • This will be the shortest way to write your query:

    SELECT 
    t, v, fqn
FROM
    [finalallinput] timestamp by t
WHERE 
    IsFirst(minute, 1) OVER (WHEN [fqn] not like '%Production%') = 1

    But you can also do the same using TopOne aggregate function:

    WITH step1 AS
(
SELECT
    TopOne() OVER (ORDER BY t ASC) firstEvent,
    fqn, min(t) as timeslot
FROM
    [finalallinput] timestamp by t
WHERE 
    fqn not like '%Production%' 
GROUP BY
    TumblingWindow(minute, 1),fqn
)

SELECT 
    firstEvent.v,
    fqn,
    timeSlot
INTO
[finalalloutput]
FROM step1