Query using ESSQL Conditional (CASE) Function missing some records

[girvandip]

Hello,

I am working with Canvas in Kibana. Below is the example of the data in tabular format:

| timestamp |  Id  |    message     |
---------------------------------
| 08:49:10  |  1   |     TE         |
| 08:50:20  |  1   |     BE         |
| 09:00:10  |  1   |     Success    |
| 09:00:50  |  2   |     TE         |
| 09:01:20  |  2   |     BE         |
| 09:20:00  |  3   |     BE         |
| 09:20:10  |  3   |     Success    |
| 09:20:30  |  4   |     TE         |

I am trying to display a metric in Canvas where it counts the number of Ids where their last message (the message with the highest timestamp) is "Success". In the given dataset above, the metric would display "2".

I have used the following expression and it works fine until it reached a certain number of records:

filters
| essql
 query={​​​​​​​​string"SELECT(CASE WHEN first(message)='Success' THEN 1 ELSE 0 END)
 as cnt FROM \"" {​​​​​​​​var"IndexName"}​​​​​​​​ "\" WHERE Id IS NOT NULL
group by Id"}​​​​​​​​
| math"sum(cnt)"
| metric
 metricFont={​​​​​​​​font family="Arial, sans-serif" size=36 align="center" color="#93969D" weight="normal" underline=false italic=false}​​​​​​​​ 
 labelFont={​​​​​​​​font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}​​​​​​​​ metricFormat="0,0.[000]"
| render

However, the issue here is that when it reached a certain number of records, all of a sudden the query returned a wrong calculation.

Are there any limitations in Canvas that I am not aware of here?

Many thanks

[markov00 (Marco Vettorello)]

Hi, as described here: SQL Limitations | Elasticsearch Guide [7.13] | Elastic there are some limitations on the number of rows returned by ESSQL.
What is the cardinality of the Id field?
Probably you can transform your ESSQL query to:

SELECT  COUNT(DISTINCT Id) as cnt FROM index where message = 'Success'