Return another column value based on Max value from Column

Question

I would like to get a value from a column based on multiple other columns. I am sure this is not that difficult, I am not sure how to go about it. This is table I am pulling the data from.

	personid	coursename	termname	task	date	passingscore
1	123456	Algebra I	Semester 1	Semester	01/04/2022	1
2	123456	Algebra I	Semester 1	Progress	01/02/2022	0
3	123456	Algebra I	Semester 2	Semester
4	123456	Algebra I	Semester 2	Progress	01/21/2022	1
5	123456	English I	Semester 1	Semester	01/21/2022	1
6	123456	English I	Semester 1	Progress	12/17/2021	1
7	123456	English I	Semester 2	Semester
8	123456	English I	Semester 2	Progress	01/22/2022	1
9	456255	Geometry	Semester 1	Progress	12/15/2021	0
10	456255	Geometry	Semester 1	Semester	01/12/2022	0
11	456255	Geometry	Semester 2	Progress	01/25/2022	0
12	456255	Geometry	Semester 2	Semester	01/31/2022	1
13	456255	Physics 1	Semester 2	Semester	01/06/2022	1
14	456255	Physics 1	Semester 2	Progress	12/12/2021	0
15	456255	Physics 1	Semester 2	Semester
16	456255	Physics 1	Semester 2	Progress	01/25/2022	1

So I would like to have them grouped by personid,coursename,termname and get the max from the date. Another way to look at it is I always want the most recent grade from each semester in each course for each student. I would then like to have the passingscore column reported back

So this is what I want.

	personid	coursename	termname	passingscore
1	123456	Algebra I	Semester 1	1
2	123456	Algebra I	Semester 2	1
3	123456	English I	Semester 1	1
4	123456	English I	Semester 2	1
5	456255	Geometry	Semester 1	0
6	456255	Geometry	Semester 2	1
7	456255	Physics 1	Semester 2	1
8	456255	Physics 1	Semester 2	1

This is what I have written. I have everything working, I just cannot figure out how to have it get the passing score. I have a feeling it is something simple I am missing.

SELECT
    t.personid, t.coursename, t.termname,
    MAX(t.date) AS Recent,
FROM 
    table1 t
GROUP BY 
    t.personID, t.courseName, t.termName

Thanks for any help.

Answer 1

You're looking for the most recent row per group which you can do using row_number

select t.personid, t.coursename, t.termname, t.Passingscore
from (
  select *, 
    Row_Number() 
      over (partition by personID, courseName, termName order by [date] desc) rn
  from t
)t
where rn=1;