I am using Pinot 0.9.1 I wanted to know how order by works The below query returns a response

select taskName, taskResult, distinctcount(personId)  from events where accountId = 1100609261882 AND workId = '40d9652e-c543-4bd2-aa4d-a11c7b23a6df' group by taskName, taskResult order by mode(createdOn) asc limit 10000

but this throws an exception

select taskName, taskResult, distinctcount(personId)  from events where accountId = 1100609261882 AND workId = '40d9652e-c543-4bd2-aa4d-a11c7b23a6df' group by taskName, taskResult order by createdOn asc limit 10000

[
  {
    "message": "QueryExecutionError:\nProcessingException(errorCode:450, message:InternalError:\njava.lang.NullPointerException\n\tat org.apache.pinot.core.operator.combine.GroupByOrderByCombineOperator.mergeResults(GroupByOrderByCombineOperator.java:230)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:120)\n\tat org.apache.pinot.core.operator.combine.BaseCombineOperator.getNextBlock(BaseCombineOperator.java:50)",
    "errorCode": 200
  }
]

mode(createdOn) asc

is the difference what makes it work ..is this a bug ?

you’d have to explicitly add createdOn into the group by clause. Otherwise if you group as (taskName, taskResult) -> (distinctCount(personId)) what is the created on that should we considered? there can be several in each group rt?

So the reason why I need to add order by to the query is so that

taskName,taskResult ,distinctcount

will be sorted by createdOn in other words the first taskName and taskResult will be displayed first in the output …I want a consistent or a guaranteed sort order either ascending or descending. I don’t want to group by each createdOn timestamp

in that case, can you put max(createdOn) in the selection, and order by that?

@User thanks for your quick response.Above query works so does the below query.Is max(createdOn) needed in select clause ?

select taskName, taskResult, distinctcount(personId)  from events where accountId = 1100609261882 AND workId = '40d9652e-c543-4bd2-aa4d-a11c7b23a6df' group by taskName, taskResult order by max(createdOn) desc limit 10000

ah, yes, you dont need it in projection

Awesome thanks