# How can we achieve an equivalent of this SQL a query in Elasticsearch?

**URL:** <https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655>\
**Category:** Elasticsearch\
**Created:** [January 15, 2015, 8:13am UTC](https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655 "2015-01-15T08:13:34Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lokesh\_Gupta](https://avatars.discourse-cdn.com/v4/letter/l/8baadc/32.png) [@Lokesh\_Gupta](https://discuss.elastic.co/u/Lokesh_Gupta)\
**Post date:** [January 15, 2015, 8:13am UTC](https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655/1 "2015-01-15T08:13:34Z")

</div>

What will be equivalent of the following query in the Elasticsearch world..

select myDate, col1, col2 from myTable  
where myDate = (select max(myDate) from myTable)

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**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:** [January 15, 2015, 8:23am UTC](https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655/2 "2015-01-15T08:23:50Z")

</div>

I think you need to run two queries for now. One is an aggregation (max). The other one use the result of this aggregation to search for documents.

My 2 cents

--  
David Pilato | Technical Advocate | [Elasticsearch.com](http://Elasticsearch.com)  
@dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) | @scrutmydocs [https://twitter.com/scrutmydocs](https://twitter.com/scrutmydocs)

> Le 15 janv. 2015 à 09:13, Lokesh Gupta [lgupta1@gmail.com](mailto:lgupta1@gmail.com) a écrit :
> 
> What will be equivalent of the following query in the Elasticsearch world..
> 
> select myDate, col1, col2 from myTable  
> where myDate = (select max(myDate) from myTable)
> 
> --  
> You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com) [mailto:elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
> To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com) [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm_medium=email&utm_source=footer).  
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout) [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/335F42ED-A70A-4401-82A6-6828DF3D794B%40pilato.fr](https://groups.google.com/d/msgid/elasticsearch/335F42ED-A70A-4401-82A6-6828DF3D794B%40pilato.fr).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Lokesh\_Gupta](https://avatars.discourse-cdn.com/v4/letter/l/8baadc/32.png) [@Lokesh\_Gupta](https://discuss.elastic.co/u/Lokesh_Gupta)\
**Post date:** [January 15, 2015, 1:05pm UTC](https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655/3 "2015-01-15T13:05:22Z")

</div>

Thanks.. Any other creative solutions?

On Thursday, January 15, 2015 at 1:54:10 PM UTC+5:30, David Pilato wrote:

> I think you need to run two queries for now. One is an aggregation (max).  
> The other one use the result of this aggregation to search for documents.
> 
> My 2 cents
> 
> --  
> _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)  
> [http://Elasticsearch.com](http://Elasticsearch.com)_  
> @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr  
> [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) | @scrutmydocs  
> [https://twitter.com/scrutmydocs](https://twitter.com/scrutmydocs)
> 
> Le 15 janv. 2015 à 09:13, Lokesh Gupta \<[lgu...@gmail.com](mailto:lgu...@gmail.com) \<javascript:\>\> a  
> écrit :
> 
> What will be equivalent of the following query in the Elasticsearch world..
> 
> select myDate, col1, col2 from myTable  
> where myDate = (select max(myDate) from myTable)
> 
> --  
> You received this message because you are subscribed to the Google Groups  
> "elasticsearch" group.  
> To unsubscribe from this group and stop receiving emails from it, send an  
> email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \<javascript:\>.  
> To view this discussion on the web visit  
> [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com)  
> [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm_medium=email&utm_source=footer)  
> .  
> For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/906c817f-3ca4-4a7b-a0cc-a316076ae332%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/906c817f-3ca4-4a7b-a0cc-a316076ae332%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood\_2](https://avatars.discourse-cdn.com/v4/letter/m/c77e96/32.png) [@Mark\_Harwood\_2](https://discuss.elastic.co/u/Mark_Harwood_2)\
**Post date:** [January 15, 2015, 1:40pm UTC](https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655/4 "2015-01-15T13:40:51Z")

</div>

Sorted query?

GET /myIndex/\_search  
{  
"query":{"match\_all": {}},  
"fields":["myDate","col1"],  
"sort": [  
{  
"myDate": {  
"order": "desc"  
}  
}  
]  
}

On Thursday, January 15, 2015 at 1:05:22 PM UTC, Lokesh Gupta wrote:

> Thanks.. Any other creative solutions?
> 
> On Thursday, January 15, 2015 at 1:54:10 PM UTC+5:30, David Pilato wrote:
> 
> > I think you need to run two queries for now. One is an aggregation (max).  
> > The other one use the result of this aggregation to search for documents.
> > 
> > My 2 cents
> > 
> > --  
> > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)  
> > [http://Elasticsearch.com](http://Elasticsearch.com)_  
> > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr  
> > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) | @scrutmydocs  
> > [https://twitter.com/scrutmydocs](https://twitter.com/scrutmydocs)
> > 
> > Le 15 janv. 2015 à 09:13, Lokesh Gupta [lgu...@gmail.com](mailto:lgu...@gmail.com) a écrit :
> > 
> > What will be equivalent of the following query in the Elasticsearch  
> > world..
> > 
> > select myDate, col1, col2 from myTable  
> > where myDate = (select max(myDate) from myTable)
> > 
> > --  
> > You received this message because you are subscribed to the Google Groups  
> > "elasticsearch" group.  
> > To unsubscribe from this group and stop receiving emails from it, send an  
> > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com).  
> > To view this discussion on the web visit  
> > [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com)  
> > [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm_medium=email&utm_source=footer)  
> > .  
> > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/8dffc8cf-8dee-4584-8fac-119482ea0831%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/8dffc8cf-8dee-4584-8fac-119482ea0831%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<div class="post-metadata">

**Author:** ![Lokesh\_Gupta](https://avatars.discourse-cdn.com/v4/letter/l/8baadc/32.png) [@Lokesh\_Gupta](https://discuss.elastic.co/u/Lokesh_Gupta)\
**Post date:** [January 15, 2015, 1:51pm UTC](https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655/5 "2015-01-15T13:51:00Z")

</div>

Thanks for the suggestion. Sorted query would work if I am okay with  
getting data for dates other than the max(date). But in the use case I have  
I need to restrict the results to be only for max(date).

Is there a way to chain the output of a query as an input to another query?

On Thursday, January 15, 2015 at 7:10:51 PM UTC+5:30, Mark Harwood wrote:

> Sorted query?
> 
> GET /myIndex/\_search  
> {  
> "query":{"match\_all": {}},  
> "fields":["myDate","col1"],  
> "sort": [  
> {  
> "myDate": {  
> "order": "desc"  
> }  
> }  
> ]  
> }
> 
> On Thursday, January 15, 2015 at 1:05:22 PM UTC, Lokesh Gupta wrote:
> 
> > Thanks.. Any other creative solutions?
> > 
> > On Thursday, January 15, 2015 at 1:54:10 PM UTC+5:30, David Pilato wrote:
> > 
> > > I think you need to run two queries for now. One is an aggregation  
> > > (max). The other one use the result of this aggregation to search for  
> > > documents.
> > > 
> > > My 2 cents
> > > 
> > > --  
> > > _David Pilato_ | _Technical Advocate_ | _[Elasticsearch.com](http://Elasticsearch.com)  
> > > [http://Elasticsearch.com](http://Elasticsearch.com)_  
> > > @dadoonet [https://twitter.com/dadoonet](https://twitter.com/dadoonet) | @elasticsearchfr  
> > > [https://twitter.com/elasticsearchfr](https://twitter.com/elasticsearchfr) | @scrutmydocs  
> > > [https://twitter.com/scrutmydocs](https://twitter.com/scrutmydocs)
> > > 
> > > Le 15 janv. 2015 à 09:13, Lokesh Gupta [lgu...@gmail.com](mailto:lgu...@gmail.com) a écrit :
> > > 
> > > What will be equivalent of the following query in the Elasticsearch  
> > > world..
> > > 
> > > select myDate, col1, col2 from myTable  
> > > where myDate = (select max(myDate) from myTable)
> > > 
> > > --  
> > > You received this message because you are subscribed to the Google  
> > > Groups "elasticsearch" group.  
> > > To unsubscribe from this group and stop receiving emails from it, send  
> > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com).  
> > > To view this discussion on the web visit  
> > > [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com)  
> > > [https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com?utm_medium=email&utm_source=footer)  
> > > .  
> > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

--  
You received this message because you are subscribed to the Google Groups "elasticsearch" group.  
To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com).  
To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/8973324f-32fc-4b90-b549-df014808d729%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/8973324f-32fc-4b90-b549-df014808d729%40googlegroups.com).  
For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout).

---

<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 6, 2017, 12:38am UTC](https://discuss.elastic.co/t/how-can-we-achieve-an-equivalent-of-this-sql-a-query-in-elasticsearch/21655/6 "2017-07-06T00:38:41Z")

</div>