Did you know that the subquery functionality within PS Query can be used to identify a minimum or maximum value of a field, where multiple may exist? Some student-records-related examples of this are a first term of enrollment or most recent academic standing for currently enrolled students, or the maximum effective-dated academic requirements report stored in the SIS for particular students.
After a query is written to identify current students in the desired program or plan, typically using STDNT_CAR_TERM and the term record, another instance of STDNT_CAR_TERM should be added, deselecting the auto-join term criteria between the two instances. TERM_TBL has been added to display the term description.
Criteria is added on the field STRM from record E, equal to a subquery.
The subquery is defined, adding the record STDNT_CAR_TERM (again!). Subqueries allow for one field only. In this case, the field is STRM, and it is aggregated to select the minimum value.
Criteria is defined to join the subquery instance of STDNT_CAR_TERM back to the second top-level instance (Record E). Criteria is also added on units and withdraw code, to ensure the term selected had enrollment and was not withdrawn from. This particular criteria will identify the first term of enrollment on the same career and career number as the top-level instance. This could result in multiple first terms, depending on the SIS setup and business practices. To get the true first term at the institution, do not join by ACAD_CAREER nor by STDNT_CAR_NBR.
Navigate back to the top level, add any necessary fields, and save!!
Next month: Did You Know that Subqueries Can Do this Other Thing?