# Equivalent to SQL window functions **URL:** \ **Category:** Elasticsearch\ **Created:** [March 16, 2016, 8:39am UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527 "2016-03-16T08:39:20Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![sergeyb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergeyb/32/5574_2.png) [@sergeyb](https://discuss.elastic.co/u/sergeyb)\ **Post date:** [March 16, 2016, 8:39am UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/1 "2016-03-16T08:39:20Z")
Is there any way to implement SQL window functions? **Here is my use case:** Multiple events reported with "Type" and "Timestamp" fields. I'm looking for a way to extract the latest occurrence of each type. **In SQL this can be easily achieved:** This is how my data looks like: ![](https://us1.discourse-cdn.com/elastic/original/2X/8/807ac525fa6abd119fbfe25faf8ef1b3fa910938.png) ..and this is the desired output: ![](https://us1.discourse-cdn.com/elastic/original/2X/2/20ccd832aa7813f75229167086e215fe944c91cd.png) The following query achieves just that: ` SELECT [Type],[Description] FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY [Type] ORDER BY [Timestamp] DESC) AS [RowNumber], [Type], [Description] FROM [test] ) T WHERE T.[RowNumber] = 1 ` Yeah, I know Elastic is not a relational database. However, I was hoping this is feasible and I'm just unable to find out how... ---
**Author:** ![thn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thn/32/8061_2.png) [@thn](https://discuss.elastic.co/u/thn)\ **Post date:** [March 16, 2016, 11:59am UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/2 "2016-03-16T11:59:00Z")
It looks like you are looking for the latest of each type. If yes, in this case do something similar to the link below and use "max" instead of "avg" on "timestamp" field. [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-filter-aggregation.html) From my experience with ES, sometimes it requires a two-step process to get what you want. ---
**Author:** ![sergeyb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergeyb/32/5574_2.png) [@sergeyb](https://discuss.elastic.co/u/sergeyb)\ **Post date:** [March 16, 2016, 4:54pm UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/3 "2016-03-16T16:54:35Z")
Thanks for the prompt response! I believe this approach will work. Although the best I can do is 3 queries and complex logic to build them on the fly: 1. Fetch all the different types 2. Construct a query to fetch max timestamps for all those types 3. Construct a query to fetch the details (e.g. description) for given types & timestamps Can you think of a better/shorter way? ---
**Author:** ![thn](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/thn/32/8061_2.png) [@thn](https://discuss.elastic.co/u/thn)\ **Post date:** [March 16, 2016, 6:15pm UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/4 "2016-03-16T18:15:29Z")
I think 1 and 2 can be done in one query. ---
**Author:** ![nik9000](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nik9000/32/44947_2.png) [@nik9000](https://discuss.elastic.co/u/nik9000)\ **Post date:** [March 18, 2016, 3:43pm UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/5 "2016-03-18T15:43:29Z")
I should point out that pipeline aggregations offer somewhat window function like functionality. I haven't read the rest of the post because it looks like you have it covered so I'm not sure if they fit what you want. But they are a thing. ---
**Author:** ![sergeyb](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sergeyb/32/5574_2.png) [@sergeyb](https://discuss.elastic.co/u/sergeyb)\ **Post date:** [March 20, 2016, 8:29am UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/6 "2016-03-20T08:29:10Z")
Thanks guys, it looks like I don't have a choice but to implement my own logic to fetch this. Hoped I'll get away with just one query to avoid implementing a middle tier... ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 11:06pm UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/7 "2017-07-05T23:06:46Z")
---
**Author:** ![dadoonet](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dadoonet/32/137187_2.png) [@dadoonet](https://discuss.elastic.co/u/dadoonet)\ **Post date:** [March 11, 2019, 8:02am UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/8 "2019-03-11T08:02:34Z")
---
**Author:** ![bragboy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bragboy/32/23058_2.png) [@bragboy](https://discuss.elastic.co/u/bragboy)\ **Post date:** [March 11, 2019, 12:27pm UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/9 "2019-03-11T12:27:00Z")
You can solve this using field collapsing with inner-hits. ``` { "collapse": { "field": "type", "inner_hits": { "name": "order by timestamp", "size": 1, "sort": [ { "timestamp": "desc" } ] } } } ``` You need to ensure that the type field is "single-valued keyword" For more information with detailed example - [https://blog.francium.tech/sql-window-function-partition-by-in-elasticsearch-c2e3941495b6](https://blog.francium.tech/sql-window-function-partition-by-in-elasticsearch-c2e3941495b6) ES Documentation on Field collapsing - [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-collapse.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-collapse.html) ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 4, 2022, 4:16am UTC](https://discuss.elastic.co/t/equivalent-to-sql-window-functions/44527/10 "2022-11-04T04:16:35Z")