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

Elastic Stack Elasticsearch
1

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/cee4d390-a53c-4c11-ae4b-4d40023ca889%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

1 Like
2

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
@dadoonet https://twitter.com/dadoonet | @elasticsearchfr https://twitter.com/elasticsearchfr | @scrutmydocs https://twitter.com/scrutmydocs

Le 15 janv. 2015 à 09:13, Lokesh Gupta 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.
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?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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/335F42ED-A70A-4401-82A6-6828DF3D794B%40pilato.fr.
For more options, visit https://groups.google.com/d/optout.

3

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
@dadoonet https://twitter.com/dadoonet | @elasticsearchfr
https://twitter.com/elasticsearchfr | @scrutmydocs
https://twitter.com/scrutmydocs

Le 15 janv. 2015 à 09:13, Lokesh Gupta <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 <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?utm_medium=email&utm_source=footer
.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/906c817f-3ca4-4a7b-a0cc-a316076ae332%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

4

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
@dadoonet https://twitter.com/dadoonet | @elasticsearchfr
https://twitter.com/elasticsearchfr | @scrutmydocs
https://twitter.com/scrutmydocs

Le 15 janv. 2015 à 09:13, Lokesh Gupta 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.
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?utm_medium=email&utm_source=footer
.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/8dffc8cf-8dee-4584-8fac-119482ea0831%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

5

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
@dadoonet https://twitter.com/dadoonet | @elasticsearchfr
https://twitter.com/elasticsearchfr | @scrutmydocs
https://twitter.com/scrutmydocs

Le 15 janv. 2015 à 09:13, Lokesh Gupta 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.
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?utm_medium=email&utm_source=footer
.
For more options, visit 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.
To view this discussion on the web visit https://groups.google.com/d/msgid/elasticsearch/8973324f-32fc-4b90-b549-df014808d729%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.