Highly selective parent-child SQL query

When I write reports, I often times run into parent-child relationships where I only need one particular child row. And sometimes a group by simply does not cut it because max, average, sum, or some of those aggregate functions simply are not a good fit for your situation when you want some info (or the entire row) of the child that was modified last, or that has the largest amount in one column, etc.

So I use a combination the top command with an order by as seen in the query below. On the query the parents are on the SyStudent table and the children come from the AdEnroll table. There can be many AdEnrolls for a SyStudent.

For each SyStudent (parent) I’m selecting all the info of the AdEnroll (child) that was added most recently.

select stu.firstname, stu.lastname, enr.startdate, enr.gpa
from systudent stu 
inner join adenroll enr on enr.adenrollid = (
  -- get the stu's enrollment that was created most recently
  select top(1) enr2.AdEnrollId
  from AdEnroll enr2
  where enr2.systudentid = stu.systudentid
  order by enr2.dateadded desc)