...

1.5 Getting the record containing the minimum value of a specified field

 
spl-cookbook(224)

Get the record containing the minimum value of a specified field, such as finding ID of the student(s) whose math score (s) is/are the lowest in class one based on the Scores table.

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
… … … …

We use minp() function to locate the record holding the minimum value of a specified field and then get the student ID.

SPL script:

A
1 =connect(“db”)
2 =A1.query(“select * from Scores where SUBJECT=‘Math’ and CLASS=‘Class one’”)
3 =A2.minp(SCORE)
4 =A3.STUDENTID

A1 Connect to the database;
A2 Get records of class one where subject is math;
A3 minp function gets the record having the minimum score;
A4 Get student ID from A3’s record.

Sometimes there is more than one record having the minimum value. To return all those records, use @a option in minp() function:

A
3 =A2.minp@a(SCORE)
4 =A3.(STUDENTID)

A3 Get all records of class one having the minimum math score;
A4 Get the sequence of student IDs from the multiple records.

Execution result:

CLASS STUDENTID SUBJECT SCORE
Class one 5 Math 60
Class one 14 Math 60

 

spl-cookbook(224)
 
 •  216 View  •