# Can ElastiSearch handle such query?

**URL:** <https://discuss.elastic.co/t/can-elastisearch-handle-such-query/59573>\
**Category:** Elasticsearch\
**Created:** [September 1, 2016, 4:18pm UTC](https://discuss.elastic.co/t/can-elastisearch-handle-such-query/59573 "2016-09-01T16:18:11Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![uncleBens](https://avatars.discourse-cdn.com/v4/letter/u/a9a28c/32.png) [@uncleBens](https://discuss.elastic.co/u/uncleBens)\
**Post date:** [September 1, 2016, 4:18pm UTC](https://discuss.elastic.co/t/can-elastisearch-handle-such-query/59573/1 "2016-09-01T16:18:12Z")

</div>

Hi,

I have this SQL query and EVENT table in my Oracle database.

> SELECT \*  
> FROM (SELECT  
> CITY,  
> max(DATE) AS eventdate  
> FROM EVENT  
> WHERE TYPE = 'CRASH'  
> GROUP BY CITY  
> ORDER BY eventdate DESC, CITY ASC)  
> WHERE ROWNUM \< 6;

> NEW YORK 15/02/27  
> LONDON 15/02/27  
> LONDON 15/02/27  
> LONDON 15/02/11  
> LONDON 15/02/19

> EVENT: ID, NAME, DATE, CITY, TYPE

I want to do the same query in Elasticsearch using JavaAPI.  
Is it possible?

I must know 6 cities that have the latest events and their dates.

---

<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 5, 2017, 10:23pm UTC](https://discuss.elastic.co/t/can-elastisearch-handle-such-query/59573/2 "2017-07-05T22:23:10Z")

</div>