# Sorting the data based on date in Elasticseach using the transform

**URL:** <https://discuss.elastic.co/t/sorting-the-data-based-on-date-in-elasticseach-using-the-transform/366734>\
**Category:** Elasticsearch\
**Tags:** transforms\
**Created:** [September 18, 2024, 12:07pm UTC](https://discuss.elastic.co/t/sorting-the-data-based-on-date-in-elasticseach-using-the-transform/366734 "2024-09-18T12:07:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![venkatkumar229](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatkumar229/32/104663_2.png) [@venkatkumar229](https://discuss.elastic.co/u/venkatkumar229)\
**Post date:** [September 18, 2024, 12:07pm UTC](https://discuss.elastic.co/t/sorting-the-data-based-on-date-in-elasticseach-using-the-transform/366734/1 "2024-09-18T12:07:52Z")

</div>

Hi Team,

We are using a sort in our transform to sort the records based on a timestamp field by grouping them on a unique key and get only the latest record out of that. But we are facing an issue as in one use case my timestamp is exactly same for all the group of records inside for a key.  
example:

Doc1:  
txn id(unique key) -\> 12345  
date -\> Sep 17, 2024 @ 13:33:57.000  
ingest time -\> Sep 17, 2024 @ 23:33:57.000  
status -\> success

Doc2:  
txn id(unique key) -\> 12345  
date -\> Sep 17, 2024 @ 13:33:57.000  
ingest time -\> Sep 17, 2024 @ 23:36:57.000  
status -\> completed

When i am sorting always i am getting **status = success** record instead of **status = completed**. Could you please help me how elastic will sort and fetch the records in this case and is there any way to solve this issue.

Note: I cannot use ingest time for sorting as my other usecases will fail.

---

<div class="post-metadata">

**Author:** ![Alex\_Salgado-Elastic](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/alex_salgado-elastic/32/103081_2.png) [@Alex\_Salgado-Elastic](https://discuss.elastic.co/u/Alex_Salgado-Elastic)\
**Post date:** [September 18, 2024, 1:19pm UTC](https://discuss.elastic.co/t/sorting-the-data-based-on-date-in-elasticseach-using-the-transform/366734/2 "2024-09-18T13:19:20Z")

</div>

Hi @venkatkumar229

Could you share your query here to clarify?

---

<div class="post-metadata">

**Author:** ![venkatkumar229](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/venkatkumar229/32/104663_2.png) [@venkatkumar229](https://discuss.elastic.co/u/venkatkumar229)\
**Post date:** [September 18, 2024, 2:19pm UTC](https://discuss.elastic.co/t/sorting-the-data-based-on-date-in-elasticseach-using-the-transform/366734/3 "2024-09-18T14:19:29Z")

</div>

Hi @Alex_Salgado-Elastic ,

Please find the below transform config.

```auto
POST _transform/_preview
{
  "source": {
    "index": [
      "xxxxxxxxxxx"
    ],
    "query": {
		"match_all": {}
    }
  },
  "dest": {
    "index": "xxxxxxxxx"
  },
  "sync": {
    "time": {
      "field": "ingest.time",
      "delay": "60s"
    }
  },
  "pivot": {
    "group_by": {
      "txn_id": {
        "terms": {
          "field": "txn_id"
        }
      }
    },
    "aggregations": {
      "date": {
        "max": {
          "field": "date"
        }
      },
      "base_fields": {
        "top_metrics": {
          "metrics": [
            {
              "field": "txn_type",
              "missing": ""
            },
            {
              "field": "status",
              "missing": ""
            }
          ],
          "sort": [{
            "date": "desc"
          }]
        }
      }
    }
  },
  "settings": {}
}

```