Unidata查询：如何选取单学生最新记录？是否有可用函数/命令？

Absolutely! In Unidata's UniQuery, there are a couple of straightforward ways to pull each student's latest term record based on the Term_Start_date. Let's break down two reliable approaches that fit your data perfectly:

Method 1: Use MAX() Aggregate with a Subquery #

This approach first finds the latest Term_Start_date for each student, then matches those dates back to the original table to get full records.

Assuming your table is named STUDENT_TERMS, here's the query:

SELECT STUDENT_TERMS
WHERE TERM_START_DATE IN (
    SELECT MAX(TERM_START_DATE)
    FROM STUDENT_TERMS
    GROUP BY STUDENT
)
BY STUDENT

Key Notes: #

• If Term_Start_date is stored as a string (like '08/01/2018' instead of a native date type), convert it to a date value first to ensure accurate max calculations:

  SELECT STUDENT_TERMS
  WHERE ICONV(TERM_START_DATE, 'D') IN (
      SELECT MAX(ICONV(TERM_START_DATE, 'D'))
      FROM STUDENT_TERMS
      GROUP BY STUDENT
  )
  BY STUDENT
  

• This will return all records matching the latest date per student. If a student has multiple records on the same latest date, all will show up—adjust if you need only one (see Method 2).

Method 2: Sort and Grab the Top Record per Student #

This is a simpler approach if you want exactly one record per student (the most recent one, even if there are ties). We sort the data so the latest record is first in each student group, then pull just that first entry.

Query:

SORT STUDENT_TERMS BY STUDENT D-S TERM_START_DATE D-S
HEAD 1 BY STUDENT

Key Notes: #

• The D-S flag sorts Term_Start_date in descending order, putting the newest date at the top of each student's group.
• Again, if Term_Start_date is a string, use the converted date for sorting to avoid alphabetical order issues:

  SORT STUDENT_TERMS BY STUDENT D-S ICONV(TERM_START_DATE, 'D') D-S
  HEAD 1 BY STUDENT
  

• If you want to break ties (e.g., same start date but different terms), add TERM D-S to the sort criteria to prioritize the latest term name.

Both methods will return your desired output:

Student Term Program Term_Start_date
1, 2018Fall , ABC, 08/01/2018
2, 2017Fall, ENG, 08/01/2017

内容的提问来源于stack exchange，提问作者Madhu