...

1.12 Top/Bottom N: getting corresponding records

 
spl-cookbook(224)

Get records containing top/bottom N values of a specified field, such as finding IDs of students whose scores rank in top 2 for each subject.

CLASS STUDENTID SUBJECT SCORE
Class one 1 English 84
Class one 1 Math 77
Class one 1 PE 69
Class one 2 English 81
Class one 2 Math 80
… … … …

SPL script:

A
1 =connect(“db”)
2 =A1.query@x(“select * from Scores”)
3 =A2.group(CLASS,SUBJECT;~.top(-2; SCORE):TOP2)
4 =A3. conj(TOP2)

A1 Connect to the database;
A2 Query students’ scores;
A3 Group A2’s records by class and subject and from each group get top 2 records in terms of score. Note: The semicolon means returning records having the values ranking in top 2;
A4 Concatenate all top 2 records for each subject in every class into a sequence.

Execution result:

CLASS STUDENTID SUBJECT SCORE
Class one 4 English 96
Class one 9 English 93
Class one 13 Math 97
Class one 10 Math 97
… … … …
spl-cookbook(224)
 
 •  210 View  •