GlideAggregate MAX

[Stanley Martin]

I have imported a number of records from Oracle into ServiceNow.  Have an Integer field called student_id that the records loaded just fine.  The last student_id loaded is #90358.  When I run the following GlideAggregate:

var gaStudent = new GlideAggregate('student');
gaStudent.addAggregate('MAX', 'student_id');
gaStudent.query();
if (gaStudent.next()) {
    gs.info('Last Student ID = ' + gaStudent.getAggregate('MAX', 'student_id'));
}

The last student_id printed in the log is 1.  

I then loaded all the student ids into an array and grabbed the MAX value in the array:

 var gr = new GlideRecord('student');
 gr.orderBy('student_id');
gr.query();
var log = [];
while (gr.next()) {
        log.push(gr.student_id);
}

var max_of_array = Math.max.apply(Math, log);
gs.info('Last Id: {0}', max_of_array);

This prints the correct value but takes a bit longer to process.

Any issues with my query?

[Amit Gujarathi]

HI @Stanley Martin ,
I trust you are doing great.
the direct casting in GlideAggregate might not be possible, a workaround is to use an encoded query to filter out non-numeric values before running the aggregate. This way, you ensure that only numeric values are considered for the aggregation.

var gaStudent = new GlideAggregate('student');
gaStudent.addEncodedQuery('student_idISNOTEMPTY^student_id>=0'); // This ensures only numeric values are considered
gaStudent.addAggregate('MAX', 'student_id');
gaStudent.query();
if (gaStudent.next()) {
    gs.info('Last Student ID = ' + gaStudent.getAggregate('MAX', 'student_id'));
}

[Stanley Martin]

Same issue.  Keeps coming back with 1 as the last student id.

[ralvarez]

By default, GlideAggregate groups results. You should explicitly disable grouping using setGroup(false), like this:

var gaStudent = new GlideAggregate('student');
gaStudent.addAggregate('MAX', 'student_id');
gaStudent.setGroup(false); // Important: disables grouping
gaStudent.query();
if (gaStudent.next()) {
    gs.info('Last Student ID = ' + gaStudent.getAggregate('MAX', 'student_id'));
}

On a side note, for your case, you can just use GlideRecord with orderByDesc and setLimit(1) to achieve the same result more directly:

var gr = new GlideRecord('student');
gr.orderByDesc('student_id');
gr.setLimit(1);
gr.query();
if (gr.next()) {
    gs.info('Last Student ID = ' + gr.student_id);
}

[ankittyagi4]

HI @Stanley Martin ,
I hope you are good , the thing is glide aggregate by default groups the results, so you should add one thing

setGroup(false);

this will resolve your error

here is the corrected version below try it and let me know if it works or not

var gaStudent = new GlideAggregate('student');
gaStudent.addAggregate('MAX', 'student_id');
gaStudent.setGroup(false); // Important: disables grouping
gaStudent.query();
if (gaStudent.next()) {
    gs.info('Last Student ID = ' + gaStudent.getAggregate('MAX', 'student_id'));
}