# Filter using max value of a column

**URL:** <https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [June 3, 2020, 3:34pm UTC](https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591 "2020-06-03T15:34:49Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![monomeric](https://avatars.discourse-cdn.com/v4/letter/m/6f9a4e/32.png) [@monomeric](https://discuss.elastic.co/u/monomeric)\
**Post date:** [June 3, 2020, 3:34pm UTC](https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591/1 "2020-06-03T15:34:49Z")

</div>

Hello!

I have an index that collects entries in batches, identified by their build number in a corresponding column, and need to show only the data from the last build on a canvas. While I can filter for an explicitly given number, I thought I would need to use something like

```auto
filters
| essql query="SELECT accession, comment, build FROM \"general*\""
| filterrows { getCell "build" | eq math "max(build)" }
| table
| render

```

but the resulting table is empty. Replacing `math "max(build)"` by a static number works fine.

Is there a way to implement such a max-filter on a Kibana Canvas?

Glad for any alternative suggestion!

Thanks,  
Mona

---

<div class="post-metadata">

**Author:** ![corey.robertson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/corey.robertson/32/54611_2.png) [@corey.robertson](https://discuss.elastic.co/u/corey.robertson)\
**Post date:** [June 3, 2020, 6:19pm UTC](https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591/2 "2020-06-03T18:19:53Z")

</div>

Hi @monomeric

I see a few issues with your expression. First, the result of each function is passed on as the input to the next function. So when you do

```auto
getCell "build" | eq math "max(build)"

```

The result of the `getCell` is the input of the `eq` function and that's all the context that the eq function has. So, doing `max(build)` will throw an error because it's doesn't know how to find the max value of a column named build when it's only given a number (the result of the getCell).

Now, it's not throwing an error because of a small syntax error on the eq function. You would have to give the `eq` function an expression for its argument using `{}` to be able to use an additional function like math, like `eq {math "max(build)"}`. As it is, it's being interpreted as just two strings, "math" and "max(build)", the second string being compared to the result of getCell, causing the empty table.

Now, to fix it, the easiest way would be to just add a new column to your result so in your `filterrows` function you can do a comparison on two columns in the same row. Something like

```auto
essql ...
| staticColumn name="max_build" value={math "max(build)"}
| filterrows fn={math "subtract(build, max_build)" | eq 0}

```

Hope that helps

---

<div class="post-metadata">

**Author:** ![monomeric](https://avatars.discourse-cdn.com/v4/letter/m/6f9a4e/32.png) [@monomeric](https://discuss.elastic.co/u/monomeric)\
**Post date:** [June 3, 2020, 8:58pm UTC](https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591/3 "2020-06-03T20:58:38Z")

</div>

Hi @corey.robertson

Many thanks for your input and for correcting my syntax. As you noticed, I am quite new to this flavour of filtering and I really appreciate your help.

Your suggestion works in principle, but it looks like I am running into Kibana issue [#51191](https://github.com/elastic/kibana/issues/51191) now, as my index currently contains about 100k entries and the max seems to be only evaluated among the first 1000 rows of my query result. Is there a workaround/fix?

---

<div class="post-metadata">

**Author:** ![monomeric](https://avatars.discourse-cdn.com/v4/letter/m/6f9a4e/32.png) [@monomeric](https://discuss.elastic.co/u/monomeric)\
**Post date:** [June 4, 2020, 9:04am UTC](https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591/4 "2020-06-04T09:04:17Z")

</div>

I understand that also with `esdocs` there is a limit of 10000 documents. Is there a way to apply the max-filter to larger indexes in Canvas?

---

<div class="post-metadata">

**Author:** ![corey.robertson](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/corey.robertson/32/54611_2.png) [@corey.robertson](https://discuss.elastic.co/u/corey.robertson)\
**Post date:** [June 5, 2020, 1:34pm UTC](https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591/5 "2020-06-05T13:34:46Z")

</div>

What if you add an ordering to your sql to order by build desc, so you are only getting the largest values in the first few rows of the result.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 3, 2020, 1:37pm UTC](https://discuss.elastic.co/t/filter-using-max-value-of-a-column/235591/6 "2020-07-03T13:37:45Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.