Hi all, Is there a way to group by a column and return documents with max(some_col) For instance I have many offers for tomato. All of them have the same pid. Can I group_by and then do max(price) ?

{
  "searches": [
    {
      "collection": "t",
      "q": "*",
      "query_by": "pid",
      "group_by": "pid",
      "page": 1,
      "per_page": 100
    }
  ]
}

{
  "results": [
    {
      "facet_counts": [],
      "found": 2,
      "found_docs": 4,
      "grouped_hits": [
        {
          "found": 2,
          "group_key": [
            "2"
          ],
          "hits": [
            {
              "document": {
                "id": "3",
                "pid": "2",
                "price": 21
              },
              "highlight": {},
              "highlights": []
            },
            {
              "document": {
                "id": "2",
                "pid": "2",
                "price": 20
              },
              "highlight": {},
              "highlights": []
            }
          ]
        },
        {
          "found": 2,
          "group_key": [
            "1"
          ],
          "hits": [
            {
              "document": {
                "id": "1",
                "pid": "1",
                "price": 11
              },
              "highlight": {},
              "highlights": []
            },
            {
              "document": {
                "id": "0",
                "pid": "1",
                "price": 10
              },
              "highlight": {},
              "highlights": []
            }
          ]
        }
      ],
      "out_of": 4,
      "page": 1,
      "request_params": {
        "collection_name": "t",
        "first_q": "*",
        "per_page": 100,
        "q": "*"
      },
      "search_cutoff": false,
      "search_time_ms": 3
    }
  ]
}

Hi Ashok, We don't support it. But could you try grouping by pid, setting group_limit=1 and sorting by price:desc?

Hi Alan, Thanks for responding.. But we already have other keys like name, etc on which we want to sort. So we cannot do sort on price. Is there any reason why max() is not supported? And any plans to bring aggregate functions in? Also pagination works in an unexpected manner when I group. When I group, the expectation is that pagination applies on groups and not individual records. So lets say I set page size to be 1, I mean bring me all documents in the 1st group. Not 1st record in the first group. Is that a fair assumption? Thanks again... Ashok

There is no specific reason; we just didn't need it yet. Could you please open a feature request on our GitHub? https://github.com/typesense/typesense/issues Regarding pagination, I believe it due it first performing the pagination and then the grouping, which explains this behavior. Could you confirm, @Kishore ?

@Kishore Nallan tagging since Alan's message didn't tag Kishore correctly