# Finding the record for the maximum attempt

**URL:** <https://discuss.elastic.co/t/finding-the-record-for-the-maximum-attempt/231656>\
**Category:** Elasticsearch\
**Created:** [May 8, 2020, 3:54am UTC](https://discuss.elastic.co/t/finding-the-record-for-the-maximum-attempt/231656 "2020-05-08T03:54:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ankitshah692](https://avatars.discourse-cdn.com/v4/letter/a/ecccb3/32.png) [@ankitshah692](https://discuss.elastic.co/u/ankitshah692)\
**Post date:** [May 8, 2020, 3:54am UTC](https://discuss.elastic.co/t/finding-the-record-for-the-maximum-attempt/231656/1 "2020-05-08T03:54:00Z")

</div>

Hello Experts ,  
I need some help to formulate a query. Below is my index and here is what i need to do

"Find persons who has scoreTotal field matching to 300 from their **latest** attempt

This means , if i were to search 300 into this index, i should only get "Minnie" in the response. Because she is the only one who has 300 score in her latest attempt.

Currently my query gives me Minnie and Naomie. But i am interested only in Minnie. How do i make sure i always check the scoreTotal for each of the person from their latest attempt ?

Appreciate your help in this .

```
{
    "games": {
        "game": "pingPong",
        "person": [
            {
                "personName": "Dixie",
                "score": [
                    {
                        "scoreTotal": 500,
                        "attempt": 1
                    },
                    {
                        "scoreTotal": 200,
                        "attempt": 2
                    }
                ]
            },
            {
                "personName": "Minnie",
                "score": [
                    {
                        "scoreTotal": 876,
                        "attempt": 1
                    },
                    {
                        "scoreTotal": 300,
                        "attempt": 2
                    }
                ]
            },
            {
                "personName": "Naomi",
                "score": [
                    {
                        "scoreTotal": 300,
                        "attempt": 1
                    },
                    {
                        "scoreTotal": 400,
                        "attempt": 2
                    },
                    {
                        "scoreTotal": 500,
                        "attempt": 3
                    }
                ]
            }
        ]
    }
}

```

Here is my query for searching -

```
{
    "query": {
        "nested": {
            "path": "person",
            "query": {
                "nested": {
                    "path": "person.score",
                    "query": {
                        "bool": {
                            "must": [
                                {
                                    "match": {
                                        "person.score.scoreTotal": "300"
                                    }
                                }
                            ]
                        }
                    }
                }
            }
        }
    }
}
```

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [May 8, 2020, 4:00am UTC](https://discuss.elastic.co/t/finding-the-record-for-the-maximum-attempt/231656/2 "2020-05-08T04:00:49Z")

</div>

I'd think a [top hits agg](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-aggregations-metrics-top-hits-aggregation.html), sorting on the attempt, would get what you want?

---

<div class="post-metadata">

**Author:** ![ankitshah692](https://avatars.discourse-cdn.com/v4/letter/a/ecccb3/32.png) [@ankitshah692](https://discuss.elastic.co/u/ankitshah692)\
**Post date:** [May 8, 2020, 5:03am UTC](https://discuss.elastic.co/t/finding-the-record-for-the-maximum-attempt/231656/4 "2020-05-08T05:03:17Z")

</div>

Thanks for your response. Could you please provide an example of how the query would look like. The problem i was facing putting a sort inside the nested "Score". Also there could be other filters applied with this query. Like personName filter AND our score query.

Appreciate your help. I am sorry if i am asking something very easy. I am very rookie in this.

---

<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 5, 2020, 5:03am UTC](https://discuss.elastic.co/t/finding-the-record-for-the-maximum-attempt/231656/5 "2020-06-05T05:03:17Z")

</div>

Closed on June 5, 2020, 5:03am UTC

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