How to group data by one column and sort the groups by another column in ServiceNow?

[Mahesh Bachhav]

Is there a way to group data by one column and sort the groups by another column in ServiceNow using GlideAggregate()?

I tried the below code but it groups the result by both columns 'caller_id' and 'opened_at' instead of just 'caller_id' column.

var count = new GlideAggregate('incident');
count.addAggregate('COUNT', 'caller_id');
count.setGroup(true);
count.groupBy('caller_id')
count.orderBy('opened_at')
count.query();
while (count.next()) {
var caller_id= count.caller_id;
gs.info(caller_id);
gs.info(count.getEncodedQuery());
}

Output is - 

*** Script: 415d0648db379010360724f405961952
*** Script: ORDERBYopened_at^GROUPBYcaller_id,opened_at
*** Script: 62826bf03710200044e0bfc8bcbe5df1
*** Script: ORDERBYopened_at^GROUPBYcaller_id,opened_at
*** Script: a2826bf03710200044e0bfc8bcbe5ded
*** Script: ORDERBYopened_at^GROUPBYcaller_id,opened_at

[Chetan Mahajan]

Hi Mahesh,

                         Try below code

var countGroupArr = [];
var caller_id;
var count = new GlideAggregate('incident');
count.addAggregate('COUNT', 'caller_id');
count.setGroup(true);
count.groupBy('caller_id');
count.orderBy('opened_at');
count.query();
while (count.next()) {
caller_id = count.caller_id;
countGroupArr.push(caller_id);
}

countGroupArr.sort(); // sorted array
gs.info(countGroupArr);

Kindly mark correct and helpful if applicable