# ROW\_NUMBER in ES SQL

**URL:** <https://discuss.elastic.co/t/row-number-in-es-sql/257923>\
**Category:** Kibana\
**Tags:** elastic-stack-sql\
**Created:** [December 7, 2020, 11:46pm UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923 "2020-12-07T23:46:07Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Treki](https://avatars.discourse-cdn.com/v4/letter/t/e480ec/32.png) [@Treki](https://discuss.elastic.co/u/Treki)\
**Post date:** [December 7, 2020, 11:46pm UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/1 "2020-12-07T23:46:07Z")

</div>

Hello,

does the command `ROW_NUMBER()` exist for ES SQL?  
When not, is there something equivalent to it?

Can someone do an example, please?

With best regards

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 7, 2020, 11:48pm UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/2 "2020-12-07T23:48:02Z")

</div>

Can you explain a little more what you'd like to achieve with this?

---

<div class="post-metadata">

**Author:** ![Treki](https://avatars.discourse-cdn.com/v4/letter/t/e480ec/32.png) [@Treki](https://discuss.elastic.co/u/Treki)\
**Post date:** [December 8, 2020, 12:07am UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/3 "2020-12-08T00:07:21Z")

</div>

of course!  
After performing the ES SQL query in a Canvas workspace, we want that the result of the search is getting displayed in the preview of a datatable with row numbers.

---

<div class="post-metadata">

**Author:** ![Treki](https://avatars.discourse-cdn.com/v4/letter/t/e480ec/32.png) [@Treki](https://discuss.elastic.co/u/Treki)\
**Post date:** [December 8, 2020, 12:12am UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/4 "2020-12-08T00:12:13Z")

</div>

![row_numbers](https://us1.discourse-cdn.com/elastic/original/3X/8/c/8c1489d6e0f94cd194cceaeeeb7084ab94e88886.png)

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 8, 2020, 12:13am UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/5 "2020-12-08T00:13:56Z")

</div>

You could order by `customer_id`, but the results of the sum won't translate into rows so I don't think that really translates here.

---

<div class="post-metadata">

**Author:** ![Treki](https://avatars.discourse-cdn.com/v4/letter/t/e480ec/32.png) [@Treki](https://discuss.elastic.co/u/Treki)\
**Post date:** [December 8, 2020, 12:17am UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/6 "2020-12-08T00:17:42Z")

</div>

In the end, we want to add a column from 1-n to our ES SQL result where n is the numbers of elements found in the ES SQL query.

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 8, 2020, 12:22am UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/7 "2020-12-08T00:22:12Z")

</div>

There's no concept of a row ID in Elasticsearch.  
And I understand the outcome, I just don't see why it'd matter when you can sort on the customer ID? Unless there's something else specific you want to do with the row ID I am not aware of.

---

<div class="post-metadata">

**Author:** ![Treki](https://avatars.discourse-cdn.com/v4/letter/t/e480ec/32.png) [@Treki](https://discuss.elastic.co/u/Treki)\
**Post date:** [December 8, 2020, 2:17pm UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/8 "2020-12-08T14:17:34Z")

</div>

We have significant issues ordering our data table in the Canvas workpad accordingly to the timestamps.

I explain the full problem here: [Link](https://discuss.elastic.co/t/time-filter-is-not-working/258000)  
If this problem is solved then we are happy and we can continue our work.

In order to make the data table only present elements which lie between two dates, we thought to take these steps:

1. Adjust the ES SQL code so that the preview of the result is what we would like to get
2. We add an additional column so that timestamp1 and timestamp2 (timestamp1 \< timestamp2) are getting numbers which are number1\<number2
3. In the expression editor, we set 'filterColumn' to the column, which we created at step 2

We hope that you understand our request.  
We just want to filter our data table according to a given timespan.

With best regards

---

<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:** [January 5, 2021, 2:17pm UTC](https://discuss.elastic.co/t/row-number-in-es-sql/257923/9 "2021-01-05T14:17:37Z")

</div>

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