FIRST_VALUE in MS Access

Question

I have a DB2 Query. Can anyone please help me to write this query in MS Access. The highlighted row in table is the expected output. Thank you.

SELECT s.Student_ID,
first_value(DT_EFFECTIVE) OVER (PARTITION BY S.Student_ID ORDER BY DT_EFFECTIVE DESC) AS DT_EFFECTIVE, 
first_value(Student_Goal) OVER (PARTITION BY S.Student_ID ORDER BY DT_EFFECTIVE DESC) AS Student_Goal
FROM Student AS S
LEFT JOIN St_Goal AS G ON  s.Student_ID = G.Student_ID
WHERE S.Student_ID = 12345

There is a method use TOP N per group, but this requires a unique identifier field. Review http://allenbrowne.com/subquery-01.html#TopN — June7, Feb 21 '19 at 21:49
Possible duplicate of [Top n records per group sql in access](https://stackoverflow.com/questions/41220690/top-n-records-per-group-sql-in-access) — June7, Feb 21 '19 at 21:50

score 0 · Answer 1 · answered Feb 21 '19 at 22:01

You can do this with a correlated subquery:

SELECT s.Student_ID,
       (SELECT TOP (1) sg2.DT_EFFECTIVE
        FROM St_Goal as sg2
        WHERE sg2.Student_ID = S.Student_ID
        ORDER BY sg2.DT_EFFECTIVE DESC
       ) as DT_EFFECTIVE, 
       (SELECT TOP (1) DT_EFFECTIVE
        FROM St_Goal as sg2
        WHERE sg2.Student_ID = S.Student_ID
        ORDER BY sg2.DT_EFFECTIVE DESC
       ) as Student_Goal
FROM Student AS S
WHERE S.Student_ID = 12345;

You don't need the reference to St_Goal in the outer query, unless you want a bunch of duplicates.

FIRST_VALUE in MS Access

1 Answers1