Sorting on Query Results

[Sunny14]

Hello Experts,

I am querying "sysapproval_approver" table and getting below results. Is it possible to sort on updated field and get only newest record? So for each RITM approval record I should get only one record [Newest] as marked below.

Thank you so much in advance

My script is something like below. So my requirement is for each RITM number I should get ONLY one record  [Newest on updated field]. 

var rec = new GlideRecord ("sysapproval_approver");

rec.addEncodedQuery("XXXXXX");

rec.orderBy("sysapproval");

rec.query();

gs.info(rec.getRowCount());

gs.info( "RITM Number"+"   "+ "Updated on");

while(rec.next()){

    gs.info(rec.sysapproval.getDisplayValue()+";"+rec.sys_updated_on);

}

[kmohammed · Accepted solution]

---

Hi Sunny 👋 — great question! By default, GlideRecord alone doesn’t support group-by queries like SQL GROUP BY, but you can work around this by either using an aggregate GlideAggregate query or building a logic set to track the latest record for each RITM.

📌Option 1: Use GlideAggregate (if your query allows it)
GlideAggregate can help if you're grouping by sysapproval (which in your case would be the RITM reference), and getting the MAX of sys_updated_on.

Example:

var ga = new GlideAggregate("sysapproval_approver");
ga.addEncodedQuery("XXXXXX");
ga.addAggregate("MAX", "sys_updated_on");
ga.groupBy("sysapproval");
ga.query();
while(ga.next()) {
  gs.info(ga.sysapproval.getDisplayValue() + " | " + ga.getAggregate("MAX", "sys_updated_on"));
}

✅This gives you the latest updated date per RITM — then you can do a follow-up query for that record if you need the whole row.

---

📌Option 2: Use GlideRecord with a tracking object

If you need to work within GlideRecord and fetch full records, you can build a simple object to track the latest record per RITM.

Example:

var latestApprovals = {};
var rec = new GlideRecord("sysapproval_approver");
rec.addEncodedQuery("XXXXXX");
rec.orderByDesc("sys_updated_on");
rec.query();

while (rec.next()) {
  var ritm = rec.sysapproval.toString();
  if (!latestApprovals[ritm]) {
    latestApprovals[ritm] = rec.sys_id.toString();
    gs.info("Latest for RITM: " + ritm + " | " + rec.sys_updated_on);
  }
}

✅This way, since you're ordering by sys_updated_on desc, the first record you hit for each RITM is the latest one, and you skip the rest.

---

✅Summary:

• Use GlideAggregate to group by RITM and get the MAX sys_updated_on

• Or use a GlideRecord with an object tracker and order by sys_updated_on desc

Hope this helps Sunny! If it works for you, would be awesome if you could give this a 👍 or mark it helpful so others can spot it too 🚀

[Sunny14]

Thanks @kmohammed  It is working as expected. Awesome. 

[venkat917181]

Hey sunny,

Try this one!!

// Get newest record for each RITM using

GlideAggregate var ga = new GlideAggregate("sysapproval_approver");

ga.addEncodedQuery("XXXXXX");

ga.groupBy("sysapproval");

ga.addAggregate("MAX", "sys_updated_on");

ga.query();

gs.info("RITM Number" + " " + "Updated on");

while (ga.next()) {

var ritmNumber = ga.sysapproval.getDisplayValue();

var maxUpdated = ga.getAggregate("MAX", "sys_updated_on");

gs.info(ritmNumber + ";" + maxUpdated);

}

 -------------------------------------------------------

If RITM0001234 has these records:

• Record A: updated on 2025-06-01 10:00:00
• Record B: updated on 2025-06-15 14:30:00
• Record C: updated on 2025-06-10 09:15:00

MAX(sys_updated_on) will return 2025-06-15 14:30:00 (the newest/most recent).

Other aggregate functions you could use:

• MIN - finds minimum/oldest date
• COUNT - counts records per group
• AVG - finds average value
• SUM - adds up values

So MAX on a date field = "newest date" = "most recent record"

please mark as helpful if you find its helpful,

[Sunny14]

Thanks Venkat your solution is also equally great