How do I SELECT x WHERE y GROUPBY z ORDERBY date DESC LIMIT 1

[NStefanisko]

So let's say  I have a table of event types that happen on a date, but I only want the most recent occurrence of said event.
example_table:
event   | date  | outcome
event 1, jan 1 | success
event 2, jan 1 | fail
event 1, feb 1 | fail

event 2, feb 1 | fail

event 1, mar 1 | success

event 2, mar 1 | success

in SQL I would say SELECT * FROM example_table GROUPBY event ORDER BY date DESC LIMIT 1
and I would get 1 record from each group, and because the order by was date descending, it is the newest record:

event 1, mar 1 | success

event 2, mar 1 | success

While SNow seems to have OrderBy and GroupBy functions there doesn't seem to be a way to limit the records for each group. Am I missing something? How did I do the SQL equivalent in SNow?

[Manmohan K]

@NStefanisko ,

Unfortunately other fields are not available in the result set of the aggregation. There is a workaround however, you can simply gliderecord the table with the max date and get any other data tied to the record with max date.

I have modified code below to get incident short description and priority, you can do something similar with event record to get outcome

var co = new GlideAggregate('incident'); // change table name in the braces 
co.addAggregate('MAX', 'sys_updated_on');   //change parameter in the braces on which you want to sort 
co.setGroup(true);
co.groupBy('state')  ///change parameter in the braces on which you want to group by 
co.query();
while (co.next()) {

gs.print("State is - "+ co.state);
gs.print("Most recent update on - "+ co.getAggregate('MAX', 'sys_updated_on'));
var maxDate=co.getAggregate('MAX', 'sys_updated_on');
var getRec = new GlideRecord('incident');
	getRec.addQuery('sys_updated_on', maxDate);
	getRec.query();
	if (getRec.next()){
gs.print(getRec.short_description);
gs.print(getRec.priority);
}
}