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?

[nitinsharma2510]

Hey @NStefanisko ,

Can you elaborate where you will be using the records? Is it a report, script or just the list view?

[NStefanisko]

I'll be using it in a script, but the encoded query form would be of the most help, because I really don't want to have to skip records for keys that I've already seen. That is, I just want to do a while(next()) loop on the records and not have to worry about filtering out old entries for each key.

[Manmohan K]

Hi @NStefanisko ,

You can use a script similar to below. The below code gives most recently updated record for each state in incident table. You can modify the code according to the table and fields from which you want to extract data

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'));
}

[NStefanisko]

That didn't work. It is pulling the max date associated with the event, but the outcome of the event is blank. as are all the other fields associated with the record that is the date is the max value.