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.

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

SPL script:

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:

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:

SPL open source address

Download