TOP 1 on a GROUP BY query (the TOP 1 of each group)

[pontupo]

Big-picture problem: for a single training session, select a single trainer (though there can be more than one trainer). If there is no trainer (possible) select an alternate trainer or training coordinator in that order.

Specifics: I've been able to accomplish this by placing a subquery in the SELECT clause which, for each Session, goes and picks the appropriate record from the list of people attached to the Session like so:

SELECT s.Session_CompanyId,
	(SELECT TOP 1 * FROM (SELECT TOP 1 p.Pers_LastName
	 			FROM SessionAttendee sa 
					LEFT JOIN Person p
						ON (sa.Attn_PersonId = p.Pers_PersonId
							AND p.Pers_Deleted IS NULL) 
	 			WHERE sa.Attn_SessionId = s.Session_SessionId 
					AND sa.Attn_Deleted is null
					AND sa.Attn_Type = 'trainer'
	 			UNION
	 			SELECT TOP 1 p.Pers_LastName
	 			FROM SessionAttendee sa 
					LEFT JOIN Person p
						ON (sa.Attn_PersonId = p.Pers_PersonId
							AND p.Pers_Deleted IS NULL) 
	 			WHERE sa.Attn_SessionId = s.Session_SessionId 
					AND sa.Attn_Deleted is null
					AND sa.Attn_Type = 'TCTRAINER'
	 			UNION
	 			SELECT TOP 1 p.Pers_LastName
	 			FROM SessionAttendee sa 
					LEFT JOIN Person p
						ON (sa.Attn_PersonId = p.Pers_PersonId
							AND p.Pers_Deleted IS NULL) 
	 			WHERE sa.Attn_SessionId = s.Session_SessionId 
					AND sa.Attn_Deleted is null
					AND sa.Attn_Type = 'ALTTR'
	 			UNION
	 			SELECT TOP 1 p.Pers_LastName
	 			FROM SessionAttendee sa 
					LEFT JOIN Person p
						ON (sa.Attn_PersonId = p.Pers_PersonId
							AND p.Pers_Deleted IS NULL) 
	 			WHERE sa.Attn_SessionId = s.Session_SessionId 
					AND sa.Attn_Deleted is null
					AND sa.Attn_Type = 'TC'
	 			UNION
	 			SELECT TOP 1 p.Pers_LastName
	 			FROM SessionAttendee sa 
					LEFT JOIN Person p
						ON (sa.Attn_PersonId = p.Pers_PersonId
							AND p.Pers_Deleted IS NULL) 
	 			WHERE sa.Attn_SessionId = s.Session_SessionId 
					AND sa.Attn_Deleted is null
					AND sa.Attn_Type = 'TCATTEND') subQuery) AS Trainer,
FROM Session s
WHERE s.Session_Deleted IS NULL

However, now I need to pull multiple fields from the SessionAttendee table. So, rather than run this whole query once for each of the fields I want (since it can only return a single field this way), what I want to do is perform the same task within the FROM clause so that I can join it to the full record set. I've gotten closer to what I want, but being unable to use the TOP 1 statement (since I need a set, not a single record) I've come up against a wall. In essence, what I want to do is perform a TOP 1 over a GROUP BY clause (selecting the TOP record from each group). This is what I have now:

FROM Session Session
	LEFT JOIN (SELECT * FROM (SELECT sa.Attn_SessionId, MAX(sa.Attn_AttendeeId) AS Attn_AttendeeId
	 				FROM SessionAttendee sa 
	 				WHERE sa.Attn_Deleted is null
						AND sa.Attn_Type = 'trainer'
					GROUP BY sa.Attn_SessionId
					UNION
	 				SELECT sa.Attn_SessionId, MAX(sa.Attn_AttendeeId) AS Attn_AttendeeId
	 				FROM SessionAttendee sa 
	 				WHERE sa.Attn_Deleted is null
						AND sa.Attn_Type = 'TCTRAINER'
					GROUP BY sa.Attn_SessionId
	 				UNION
	 				SELECT sa.Attn_SessionId, MAX(sa.Attn_AttendeeId) AS Attn_AttendeeId
	 				FROM SessionAttendee sa 
	 				WHERE sa.Attn_Deleted is null
						AND sa.Attn_Type = 'ALTTR'
					GROUP BY sa.Attn_SessionId) subQuery1) subQuery
		ON (subQuery.Attn_SessionId = Session.Session_SessionId)
	LEFT JOIN SessionAttendee SessionAttendee 
		ON (SessionAttendee.Attn_AttendeeId = subQuery.Attn_AttendeeId
                	AND SessionAttendee.Attn_Deleted is null)

This still returns multiple records for each Session, though it only returns one record of each Type of Attendee. If I could GROUP BY subQuery by subQuery.Attn_SessionId and then do a TOP 1 applied to each group...

So, any alternative ideas on how to approach this problem or how to resolve the problem with my approach?