Sorry couldn't help it saw an empty forum thought it deserved a post :)

I am having a problem in getting some SQL to work. I have a set of User roles which are all members of a particular role group. Each user can be a member of multiple roles in the group though the expiry dates will all be different. I am trying to create a view that will contain the UserRoleID, UserID, RoleID, ExpiryDate, IsTrialUsed, EffectiveDate and RoleName of the latest expiry date for each user just for the particular role group.

I have come up with the following SQL which seems to only work when each user just has a single userrole in the group but not when there are multiple. I can't work out how to return just the 1 record for each user having the latest expiry date. Hope someone can help.

SELECT UserRoles.UserRoleID, UserRoles.UserID, UserRoles.RoleID, UserRoles.ExpiryDate, UserRoles.IsTrialUsed, UserRoles.EffectiveDate, Roles.RoleName,
Roles.RoleGroupID
FROM UserRoles INNER JOIN
Roles ON UserRoles.RoleID = Roles.RoleID INNER JOIN
(SELECT UserID, MAX(ExpiryDate) AS MaxExpDate
FROM UserRoles AS UserRoles_1
GROUP BY UserID) AS B ON UserRoles.UserID = B.UserID AND UserRoles.ExpiryDate = B.MaxExpDate
WHERE (Roles.RoleGroupID = 4)
ORDER BY UserRoles.UserID

TIA

Antony