Top Metric value for a combination of dimensions

Hi,

Is there a way to make the groupBy query return only the record with highest value of metric for a given set of dimensions or any alternative query? Consider the query below:

[{
“version”: “v1”,
“timestamp”: “2019-06-25T00:00:00.000Z”,
“event”: {
“count”: 5,
“Department”: “Dept-1”,
“Employee”: “Emp-1”
}
}, {
“version”: “v1”,
“timestamp”: “2019-06-25T00:00:00.000Z”,
“event”: {
“count”: 4,
“Department”: “Dept-1”,
“Employee”: “Emp-2”
}
}, {
“version”: “v1”,
“timestamp”: “2019-06-25T00:00:00.000Z”,
“event”: {
“count”: 5,
“Department”: “Dept-1”,
“Employee”: “Emp-3”
}
}, {
“version”: “v1”,
“timestamp”: “2019-06-25T00:00:00.000Z”,
“event”: {
“count”: 6,
“Department”: “Dept-2”,
“Employee”: “Emp-1”
}
}, {
“version”: “v1”,
“timestamp”: “2019-06-25T00:00:00.000Z”,
“event”: {
“count”: 5,
“Department”: “Dept-2”,
“Employee”: “Emp-2”
}]

``

Is there a query to return the record with highest “count” value for each “Department”? Each department can have multiple employees but we need to find the one with highest rating. Is this something that can only be achieved after post processing the above response from groupBy query or can this be achieved through the query itself? i have looked at limitSpec but wasn’t able to achieve the expected result.

Thanks,

Prathamesh

Hi Prathamesh:

Have you tried Min / Max aggregators? https://druid.apache.org/docs/latest/querying/aggregations.html

Thanks