TopOne - WHEN CLAUSE

Question

Hi,

I'm trying to use WHEN clause in top one function in stream analytic to filter the way TopOne should do the ranking, but it doesn't seem to work as expected.

https://learn.microsoft.com/en-us/stream-analytics-query/topone-azure-stream-analytics

Is there anyone who can provide an example on how to use it?

Can TopOne function be called in the SELECT part more than once, doing different ranks?

Like:

SELECT 
        deviceId,
        TopOne(fieldA) OVER (PARTITION BY deviceId ORDER BY timestamp DESC when fieldA > 0) AS firstFieldA,
        TopOne(fieldB) OVER (PARTITION BY deviceId ORDER BY timestamp DESC when fieldB is not null) AS newEnergyValue,
FROM eventHub
GROUP BY 
     deviceId,
     HoppingWindow(Duration(day,1),Hop(minute,15))

Thanks

Answer 1

Hi Augusto Piva,

Thanks for the answer, if the code you provided does not work and it looks okay to you, there might be an issue with the data or a bug in the Stream Analytics service. In that case, you might want to reach out to Azure support for further assistance.

Now, for your other question, the WHERE clause in your query can indeed affect the results of the TopOne function with the WHEN clause. For example, if you have a WHERE clause that filters out certain rows, those rows will not be available for the TopOne function to consider, even if they would have met the WHEN condition so you can try the following:

SELECT 
    deviceId,
    TopOne(fieldA) OVER (PARTITION BY deviceId ORDER BY timestamp DESC WHEN fieldA > 0) AS firstFieldA,
    TopOne(fieldB) OVER (PARTITION BY deviceId ORDER BY timestamp DESC WHEN fieldB IS NOT NULL) AS newEnergyValue
FROM eventHub
WHERE someCondition = true
GROUP BY 
    deviceId,
    HoppingWindow(Duration(day, 1), Hop(minute, 15))

I hope the information provided this time was useful.

Regards,

Gao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Hi @Gao Chen

Thanks for the response.

If you look carefully to my sample query, the WHEN clause is placed in the same order as in your example.

I do have one more question though,

Does the WHERE clause in the query affects the WHEN clause in the TopOne func? Thanks

Answer 3

Hello Augusto Piva,

Welcome to Microsoft Q&A!

Please kindly note that we do not support coding however in your code, the WHEN clause should be placed before the ORDER BY clause within the TopOne function, here's an example on how you can use it:

SELECT
    TopOne() OVER (
        PARTITION BY PartitionKey
        ORDER BY Value DESC
        LIMIT DURATION(second, 10)
        WHEN ConditionColumn = 'desired_value'
    ) AS TopEvent
FROM
    InputStream
GROUP BY
    TumblingWindow(second, 10)

Also, regarding your Select question, yes, you can call the TopOne function multiple times in the SELECT clause, each with different ranking criteria as this allows you to rank and filter data based on different conditions within the same query. I hope the information provided was useful.Best Regards,

Gao

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".