...

1.6 Getting the record containing the maximum value of a specified field

 
spl-cookbook(224)

Get the record containing the maximum value of a specified field, such as finding the nation that holds the first place on the Olympic Medal Table for the longest time.

Game Nation Gold Silver Copper
30 USA 46 29 29
30 China 38 27 23
30 UK 29 17 19
30 Russia 24 26 32
30 Korea 13 8 7
… … … … …

maxp() function is used to get the record containing the maximum value.
SPL script:

A
1 =T(“Olympic.csv”)
2 =A1.sort@z(Game, 1000000*Gold+1000*Silver+Copper)
3 =A2.group@o1(Game)
4 =A3.group@o(Nation)
5 =A4.maxp(~.len())

A1 Retrieve the Olympic medal count table;
A2 Sort A1’s table by game and total medal count in descending order;
A3 Get one record, which is the first one of the ordered records, from each game;
A4 Group A3’s records by nation;
A5 Get the longest group, which has the nation occupying the first place for the longest time.

Execution result:

Game Nation Gold Silver Copper
10 USA 41 32 30
9 USA 22 18 16
8 USA 45 27 27
7 USA 41 27 28

 

spl-cookbook(224)
 
 •  206 View  •