RaqForum 25 No.
268 View  • 
     ...

4.11 Sort groups

 
spl-cookbook(224)

We have data as follows:

Class Name Math
1 Tom 85
2 John 92
2 Joan 86
1 Rocky 85
2 Ham 92
1 Kate 83
1 Rose 75
2 Nomy 78
1 Neil 61
2 Jack 84
1 Joe 88

The task is to group data by class, and sort classes by the average score in descending order and records of each class by score in descending order:

SPL script:

A
1 =T(“score.xlsx”).group(Class;~.sort@z(Math):data,~.avg(Math):avg)
2 =A1.sort@z(avg).(data).conj()

A1 Import data, group data by Class, sort data in each group by Math in descending order, and calculate the average score in each group.
A2 Sort A1 by average score in descending order and concatenate all classes into a sequence.

Execution result:

Class Name Math
2 John 92
2 Ham 92
2 Joan 86
2 Jack 84
2 Nomy 78
1 Joe 88
1 Tom 85
1 Rocky 85
1 Kate 83
1 Rose 75
1 Neil 61

 

spl-cookbook(224)
 
 •  268 View  •