query the last record with group by

[ryuzh]

hi,

I have a "order" table with below structure:

sys_id	customer_id	product	order_date
1	AAA	Apple	2016-01-05
2	AAA	Orange	2016-09-05
3	BBB	Banana	2017-03-05
4	BBB	Pear	2017-02-05
5	CCC	Watermelon	2016-08-05
6	CCC	Grape	2017-07-05

How can I query the latest order for each customer in a script, which should return:

customer_id	product	order_date
AAA	Orange	2016-09-05
BBB	Banana	2017-03-05
CCC	Grape	2017-07-05

Thank you

Jack

[sethivarun · Accepted solution]

Hi Jack,

Following script should give you what you need

var count = new GlideAggregate('order');
count.addAggregate('COUNT');
count.groupBy('customer_id');
count.query();
while (count.next()) {   
var customer = count.customer_id.getDisplayValue();
var customerLatest= new GlideRecord('order');
customerLatest.addQuery('customer_id',customer );
customerLatest.setLimit(1);
customerLatest.orderByDesc('order_date'); 
customerLatest.query();
if(customerLatest.next()){
gs.print(customerLatest.customer_id +"   " + customerLatest.product + " " + customerLatest.order_date );
}
}

Please mark it as correct if it answers your question

[ryuzh]

Thank you Varun. I think we have to do it in two queries. but it works.

Thanks again

[Narayana1]

Thank you for posting the script and your comments. Would anyone happen to know why I see only records with no value (in the equivalent of customer_id field mentioned here) when I add the script include as filter on a report? Say i named this script include 'Xyz', my filter condition on the report was customer_id.sys_id  'is one of'  javascript: Xyz().

[PJ363182]

did you figure it out eventually?