GlideAggregate setLimit(5) returning more than 5 results in background script.

[AbW]

Please ignore the business use case.
The focus is only on understanding the GlideAggregate behavior below.
I am learning GlideAggregate and want to understand why this is happening.

I am running the following script in Background Scripts (sys.scripts):

// You’re building a dashboard widget for top 5 assignment groups with most active tasks. How can you limit the result?

var result = new  GlideAggregate('task');

result.addQuery('active', true);

result.groupBy('assignment_group');

result.addAggregate('COUNT', 'assignment_group');

result.orderByAggregate('COUNT','assignment_group');

result.setLimit(5);

result.addNotNullQuery('assignment_group');

result.query();

while(result.next()){

    gs.info(result.getDisplayValue('assignment_group') + ': ' + result.getAggregate('COUNT' , 'assignment_group' ));

}

Even though setLimit(5) is used, the script prints 6 rows.
There are definitely more than 6 groups in the data, so I expect the output to be limited to 5 rows only.

Can someone explain:

• Why setLimit(5) is not limiting the result to 5 rows

• How setLimit() behaves when used with GlideAggregate, groupBy, and orderByAggregate

Any explanation focused on the code behavior would be appreciated.

[Tanushree Maiti]

gr.setLimit(Count): This will limit the GlideRecord query to return only mentioned 'count' record.

setLimit() does not work as expected with GlideAggregate.

Unlike GlideRecord, where setLimit() limits the resulting records, GlideAggregate operates on groupings, and the limit may be applied to the underlying table scan rather than the aggregated result set. 

How to limit the result count while using GlideAggregate, you will get it from here with sample code:Counting with GlideAggregate

Please refer this link, see if it helps you:

GlideAggregate - limiting number of records - ServiceNow Community

[Aditya_hublikar]

Hello @AbW ,

Here setLimit works differently.ServiceNow first fetches X number of task records based on setLimit().Then it performs the groupBy('assignment_group').If those limited records belong to more unique assignment groups, you may see more rows than the limit value.Hence getting => setLimit(5) can return 6 grouped results

so you can either this :

var result = new  GlideAggregate('task');

result.addQuery('active', true);

result.groupBy('assignment_group');

result.addAggregate('COUNT', 'assignment_group');

result.orderByAggregate('COUNT','assignment_group');

result.setLimit(4);

result.addNotNullQuery('assignment_group');

result.query();

while(result.next()){

    gs.info(result.getDisplayValue('assignment_group') + ': ' + result.getAggregate('COUNT' , 'assignment_group' ));

}

Or this :

var result = new GlideAggregate('task');

result.addQuery('active', true);

result.addNotNullQuery('assignment_group');

result.groupBy('assignment_group');

result.addAggregate('COUNT', 'assignment_group');

result.orderByAggregate('COUNT', 'assignment_group');

result.query();

var count = 0;

while (result.next()) {

    if (count >= 5) break;

    gs.info(

        result.getDisplayValue('assignment_group') + ': ' +

        result.getAggregate('COUNT', 'assignment_group')

    );

    count++;

}

If this helps you then mark it as helpful and accept as solution.

Regards,

Aditya