Edit: In my pre-coffee rush this morning I completely missed the grouping by role (which is not that much harder FWIW). This unfortunately invalidates my entire post as it was posted and I don't want to spread misinfo.
You'd need to do some grouping in there to be able to get one employee per role instead of a single employee out of the whole data set.
Time willing I will provide an updated reply with fixed SQL.