# Getting latest record of each day

**URL:** <https://discuss.elastic.co/t/getting-latest-record-of-each-day/129449>\
**Category:** Elasticsearch\
**Created:** [April 25, 2018, 10:02am UTC](https://discuss.elastic.co/t/getting-latest-record-of-each-day/129449 "2018-04-25T10:02:36Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![veerabhadraiah](https://avatars.discourse-cdn.com/v4/letter/v/e495f1/32.png) [@veerabhadraiah](https://discuss.elastic.co/u/veerabhadraiah)\
**Post date:** [April 25, 2018, 10:02am UTC](https://discuss.elastic.co/t/getting-latest-record-of-each-day/129449/1 "2018-04-25T10:02:37Z")

</div>

Hi ,

In mysql i had query like

```auto
select id, eventIdx, sitename,machine, nid, nocstatus, reportDate,priority from (select id, eventIdx, sitename,machine, nid, nocstatus, reportDate,priority from event.tempGraphSummary where reportDate between '2018-04-04' and '2018-04-19' and priority=1 and sitename = 'notifyconsole__2018000743' order by id desc) as t group by sitename,machine,priority,reportDate,nid

```

Which gives me data of all latest records of each date and with machine list.

In ElasticSearch i tried like

```auto
{
  "size": 1000,
  "query": {
    "bool": {
      "must": [
        {"term": {"sitename": "notifyconsole__2018000743"}},
        {"term": {"priority": "1"}}
      ]
    }
  },"sort":[{"id":{"order":"asc"}}],
  "aggs": {
    "id1_count": {"terms": { "field": "nid"},
	"aggs": {
        "id2_count": {"terms": { "field": "sitename"},
          "aggs": {
              "id3_count": {"terms": { "field": "priority"},
               "aggs": {"id4_count": {"terms": { "field": "reportDate"},
                   "aggs": {"id5_count": {"terms": { "field": "machine"} }
                    }
                 }
                }
             }

          }
       }
       
    }
  }
}
}

```

But am not getting expected result please someone help me.

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [May 9, 2018, 1:02pm UTC](https://discuss.elastic.co/t/getting-latest-record-of-each-day/129449/2 "2018-05-09T13:02:37Z")

</div>

You probably need to run a date\_histogram aggregation with a sub top hits aggregation that has a size equal to 1 and sorts by decreasing id. See [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-metrics-top-hits-aggregation.html).

---

<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:** [June 6, 2018, 1:02pm UTC](https://discuss.elastic.co/t/getting-latest-record-of-each-day/129449/3 "2018-06-06T13:02:42Z")

</div>

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