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)